Postgresql query between date ranges

Try to use >= start AND < end.

Here is an example:

SELECT
  user_id
FROM
  user_logs
WHERE
      login_date >= '2014-02-01'
  AND login_date < '2014-03-01'

In this case we need to calculate the start date of the month you need, but that should be straight forward in any number of methods.

Here, the end date is also simplified; add one month. No messing about with 28th, 30th, 31st, and so on.

The advantage is , it is able to maintain use of indexes.

WHERE
      DATEPART('year', login_date) = 2014
  AND DATEPART('month', login_date) = 2

The code involves calculate the conditions for every ow in the table and not using index to find the range of match rows.

The Range Types are supported in postgre sql. So we can use this code,and this method is better than string comparison.

SELECT user_id
FROM user_logs
WHERE '[2014-02-01, 2014-03-01]'::daterange @> login_date

One can use this code for postgre sql version 8.1 or above:

SELECT user_id
FROM user_logs
WHERE login_date BETWEEN SYMMETRIC '2014-02-01' AND '2014-02-28'

    Actually, BETWEEN SYMMETRIC and BETWEEN are same, except there is no need that the argument to the left of AND be less than or equal to the argument on the right. Otherwise, the two arguments are automatically swapped. so the nonempty range is always applied.