sql - 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

Answer 1

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

For example:

SELECT
  user_id
FROM
  user_logs
WHERE
      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).