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

Gewirtz

Is the interval always a month or could it be, that you start maybe at 15th and end up on 7th of following month? – Tala

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).

Guesswarp

The first approach needs to mess around with months (instead of days) f.ex. 2014-12-01 & 2015-01-01. The second can be indexed too, but that's not trivial, i admit -- EXTRACT() seems more compatibe. – Ixion

You can avoid date calculations in your application by using interval. e.g.: WHERE login_date >= '2014-02-01' AND login_date < '2014-02-01'::date + INTERVAL '1 month' This still uses indexes while simplifying your code. – Peruse

@MatBailie, special thanks for the DATEPART notice! – Propagate

flawless answer! – Yuletide

datepart function doesn't exist - it is called date_part() – Blearyeyed

Answer 2

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 @> login_date

this should be more efficient than the string comparison

Pelson

For Anyone having TimeStamp type for login_date : SELECT user_id FROM user_logs WHERE '[2014-02-01, 2014-03-01]'::daterange @> login_date::date – Quitrent

Is it possible to build a dynamic range? I'd like to make an INNER JOIN checking if a timestamp in a table is in range from two attributes on a second table. – Stipe

It seems tsrange(lower-bound, upper-bound) does the trick! – Stipe

Very useful and quite the way to go, especially when you want to create ranges like [from, to) or (from, to], making sure left or right side doesn't match the equal case – Sextuple

Answer 3

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.

Esperanto

Be aware, that this query includes 2014-02-28 00:00:00 – Epideictic

Doc over here – Asthenosphere

Answer 4

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.

Campeche

Be aware, that this query includes 2014-03-01 00:00:00. – Epideictic

@Epideictic would you then do something like ... AND '2014-03-01' - interval '1 microsecond' to mitigate this? – Formal

I'd just use the accepted answer – Epideictic

Answer 5

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) >='2015-02-09'AND date_trunc('day',table1.date_eval) <'2015-02-09')    

Tinatinamou

This doesn't answer the question exactly. The question asks for dates based on month and year. – Donoghue

May be this doesn't answer the question, but it resolved my issues. Although, I've a question that what if the end date date will be of another month then the data will be returned will be from same month or from another month? – Vulturine