Postgresql query between date ranges
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.