Postgresql query between date ranges

With dates (and times) many things become simpler if you use >= start AND < end.

For example:

SELECT  user_idFROM  user_logsWHERE      login_date >= '2014-02-01'  AND login_date <  '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).

From PostreSQL 9.2 Range Types are supported. So you can write this like:

SELECT user_idFROM user_logsWHERE '[2014-02-01, 2014-03-01]'::daterange @> login_date

this should be more efficient than the string comparison

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.