Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting results between two dates in PostgreSQL

I have the following table:

+-----------+-----------+------------+----------+ | id        | user_id   | start_date | end_date | | (integer) | (integer) | (date)     | (date)   | +-----------+-----------+------------+----------+ 

Fields start_date and end_date are holding date values like YYYY-MM-DD.

An entry from this table can look like this: (1, 120, 2012-04-09, 2012-04-13).

I have to write a query that can fetch all the results matching a certain period.

The problem is that if I want to fetch results from 2012-01-01 to 2012-04-12, I get 0 results even though there is an entry with start_date = "2012-04-09" and end_date = "2012-04-13".

like image 964
Psyche Avatar asked Apr 16 '12 08:04

Psyche


People also ask

Is between inclusive in PostgreSQL?

The PostgreSQL BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).

How do I specify a range in PostgreSQL?

In the text form of a range, an inclusive lower bound is represented by “ [ ” while an exclusive lower bound is represented by “ ( ”. Likewise, an inclusive upper bound is represented by “ ] ”, while an exclusive upper bound is represented by “ ) ”.

What is date range in PostgreSQL?

In Postgresql, Range types are data types that represent a range of values of some element type. There are many different range types in Postgresql and daterange is one of the types that represent the range of date. Let' view the records of employees whose hire date range between 1985-11-21 and 1989-06-02.


1 Answers

 SELECT *    FROM mytable   WHERE (start_date, end_date) OVERLAPS ('2012-01-01'::DATE, '2012-04-12'::DATE); 

Datetime functions is the relevant section in the docs.

like image 128
Marco Mariani Avatar answered Oct 02 '22 03:10

Marco Mariani