Postgresql query between date ranges

Question

I am trying to query my postgresql db to return results where a date is in certain month and year  In other words I would like all the values for a month-year    The only way i ve been able to do it so far is like this   SELECT user id  FROM user logs  WHERE login date BETWEEN  2014-02-01  AND  2014-02-28    Problem with this is that I have to calculate the first date and last date before querying the table  Is there a simpler way to do this   Thanks

User · Accepted Answer

With dates  and times  many things become simpler if you use  gt   start AND  lt  end   For example   SELECT   user id FROM   user logs WHERE       login date  gt    2014-02-01    AND login date  lt    2014-03-01    In this case you still need to calculate the start date of the month you need  but that should be straight forward in any number of ways   The end date is also simplified  just add exactly one month   No messing about with 28th  30th  31st  etc     This structure also has the advantage of being able to maintain use of indexes     Many people may suggest a form such as the following  but they do not use indexes   WHERE       DATEPART  year    login date    2014   AND DATEPART  month   login date    2   This involves calculating the conditions for every single row in the table  a scan  and not using index to find the range of rows that will match  a range-seek

User · Answer

Just in case somebody land here    since 8 1 you can simply use   SELECT user id  FROM user logs  WHERE login date BETWEEN SYMMETRIC  2014-02-01  AND  2014-02-28    From the docs      BETWEEN SYMMETRIC is the same as BETWEEN except there is no   requirement that the argument to the left of AND be less than or equal   to the argument on the right  If it is not  those two arguments are   automatically swapped  so that a nonempty range is always implied

User · Answer

From PostreSQL 9 2 Range Types are supported  So you can write this like   SELECT user id FROM user logs WHERE   2014-02-01  2014-03-01    daterange   gt  login date   this should be more efficient than the string comparison

User · Answer

Read the documentation   http   www postgresql org docs 9 1 static functions-datetime html  I used a query like that   WHERE       date trunc  day  table1 date eval     2015-02-09      or  WHERE date trunc  day  table1 date eval   gt   2015-02-09 AND date trunc  day  table1 date eval   lt  2015-02-09         Juanitos Ingenier

User · Answer

SELECT user id  FROM user logs  WHERE login date BETWEEN  2014-02-01  AND  2014-03-01   Between keyword works exceptionally for a date  it assumes the time is at 00 00 00  i e  midnight  for dates

[sql] Postgresql query between date ranges

Examples related to sql

Examples related to postgresql

Examples related to date

Examples related to between