Filter by date range (same month and day) across years

tommy5115 picture tommy5115 · Aug 14, 2012 · Viewed 8.4k times · Source

I have a PostgreSQL database with a table holding dates. Now I need to find all rows within the date range 15/02 until 21/06 (day/month) across all years.

Example result:

1840-02-28
1990-06-21
1991-02-15
1991-04-25
1992-05-30
1995-03-04
1995-04-10
2001-02-03
2010-04-06

Answer

kgrittn picture kgrittn · Aug 15, 2012

Assuming (with a leap of faith) that you want dates between certain days of the year regardless of the year (like if you're sending out a batch of birthday cards or something), you can set up a test with this:

CREATE TABLE d (dt date);
COPY d FROM STDIN;
1840-02-28
1990-06-21
1991-02-15
1991-04-25
1992-05-30
1995-03-04
1995-04-10
2001-02-03
2010-04-06
\.

And you can use "row value constructors" to easily select the desired range:

SELECT * FROM d
  WHERE (EXTRACT(MONTH FROM dt), EXTRACT(DAY FROM dt))
           BETWEEN (2, 15) AND (6, 21);

Which yields:

     dt     
------------
 1840-02-28
 1990-06-21
 1991-02-15
 1991-04-25
 1992-05-30
 1995-03-04
 1995-04-10
 2010-04-06
(8 rows)