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

Question

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 1

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)

Answer 2

Use a WHERE clause with the BETWEEN operator. See:

http://www.postgresql.org/docs/current/static/functions-comparison.html#FUNCTIONS-COMPARISON

and:

http://www.postgresql.org/docs/current/static/sql-select.html http://www.postgresql.org/docs/current/static/tutorial.html

If that doesn't help, please expand your question with:

• The structure of the table(s) you're working with, either from psql's \d tablename command or the original CREATE TABLE statements;
• Some sample contents
• The query you're having problems with
• Expected results

Answer 3

You can use following syntax.

SELECT * FROM tableName WHERE dateColumnName BETWEEN '2012.01.01' AND '2012.08.14';

Just replace following;

tableName       - Name of the table you are going to access
dateColumnName  - Name of the column whch contains dates
2012.08.1       - Start date
2012.08.21      - End date 

When entering the two dates, carefully examine the example above. Enter in the same format, and enclose them inside ''s.

If you replace * mark with a column name, you can filter out values of that column only.

Hope that helps..