Getting results between two dates in PostgreSQL

Question

I have the following table    ----------- ----------- ------------ ----------    id          user id     start date   end date      integer     integer     date         date       ----------- ----------- ------------ ----------    Fields start date and end date are holding date values like YYYY-MM-DD    An entry from this table can look like this   1  120  2012-04-09  2012-04-13    I have to write a query that can fetch all the results matching a certain period    The problem is that if I want to fetch results from 2012-01-01 to 2012-04-12  I get 0 results even though there is an entry with start date    2012-04-09  and end date    2012-04-13

User · Answer

Assuming you want all  overlapping  time periods  i e  all that have at least one day in common   Try to envision time periods on a straight time line and move them around before your eyes and you will see the necessary conditions   SELECT   FROM   tbl WHERE  start date  lt    2012-04-12   date AND    end date    gt    2012-01-01   date    This is sometimes faster for me than OVERLAPS - which is the other good way to do it  as  Marco already provided    Note the subtle difference  per documentation       OVERLAPS automatically takes the earlier value of the pair as the   start  Each time period is considered to represent the half-open   interval start  lt   time  lt  end  unless start and end are equal in which   case it represents that single time instant  This means for instance   that two time periods with only an endpoint in common do not overlap    Bold emphasis mine   Performance  For big tables the right index can help performance  a lot    CREATE INDEX tbl date inverse idx ON tbl start date  end date DESC     Possibly with another  leading  index column if you have additional selective conditions   Note the inverse order of the two columns  Detailed explanation    Optimizing queries on a range of timestamps  two columns

User · Answer

Looking at the dates for which it doesn t work -- those where the day is less than or equal to 12 -- I m wondering whether it s parsing the dates as being in YYYY-DD-MM format

User · Answer

just had the same question  and answered this way  if this could help   select    from table where start date between  2012-01-01  and  2012-04-13  or    end date   between  2012-01-01  and  2012-04-13

User · Answer

To have a query working in any locale settings  consider formatting the date yourself   SELECT      FROM testbed   WHERE start date  gt   to date  2012-01-01   YYYY-MM-DD      AND end date  lt   to date  2012-04-13   YYYY-MM-DD

User · Answer

SELECT      FROM mytable   WHERE  start date  end date  OVERLAPS   2012-01-01   DATE   2012-04-12   DATE     Datetime functions is the relevant section in the docs

User · Answer

No offense but to check for performance of sql I executed some of the above mentioned solutiona pgsql    Let me share you Statistics of top 3 solution approaches that I come across   1  Took   1 58 MS Avg  2  Took   2 87 MS Avg  3  Took   3 95 MS Avg  Now try this      SELECT   FROM table WHERE DATE TRUNC  day   date    gt   Start Date AND DATE TRUNC  day   date    lt   End Date   Now this solution took   1 61 Avg   And best solution is 1st that suggested by marco-mariani

User · Answer

You have to use the date part fetching method   SELECT   FROM testbed WHERE start date    date  gt   to date  2012-09-08    YYYY-MM-DD   and date  date  lt   to date  2012-10-09    YYYY-MM-DD

User · Answer

SELECT   FROM ecs table WHERE  start date  end date  OVERLAPS   2012-01-01   DATE   2012-04-12   DATE   interval  1

[sql] Getting results between two dates in PostgreSQL

Performance

Examples related to sql

Examples related to postgresql

Examples related to date

Examples related to overlap