Postgresql select between month range

Question

I have table with one of the columns as date in 'YYYY-MM-DD' format. Can I use select to get all data in a monthly range? Say I want all the data from 2012-01-xx to 2013-04-xx. So I am basically looking for a SQL query like the one given below:

SELECT * FROM table WHERE date IN BETWEEN '2012-01' AND '2013-04' (INVALID QUERY)

Since every month begin with '01' I can modify the above query to adjust the start condition.

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND '2013-04' (INVALID QUERY)

Now the issue comes with the enddate. I have to manually calculate the last date for the given month, taking all factors in account like length of the month, leap year etc., as the query fails if the given date is invalid. So currently I am doing something like this:

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND 'VALID_MONTH_END_DATE' (VALID Query)

I want to know if there is any way to avoid this valid end date calculation?

Clarification

I have thought above the first day of the next month, but even then I'll have to apply some logic say, if its December, the next month would be January of next year. I wanted to know if a SQL only solution is possible?

Solution 1

This is a very common need in reporting environments. I have created several functions to accommodate these date manipulations

CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Then you can use...

WHERE date >= '2012-01-01' 
  AND date < fn_getlastofmonth('2013-04-01') 

Solution 2

It's good to avoid BETWEEN for date range comparisons. Better use >= and < as it works equally with date and datetime columns/values.

One way (if you can build the dates externally):

WHERE date >= DATE '2012-01-01' 
  AND date < DATE '2013-05-01'      --- first date of the next month

You could also use date arithmetic:

WHERE date >= DATE '2012-01-01' 
  AND date < DATE ('2013-04-01' + INTERVAL '1 MONTH')

or the OVERLAPS operator:

WHERE (date, date) OVERLAPS
      (DATE '2012-01-01', DATE '2013-05-01')

You should also read the Postgres documentation: Date/Time Functions and Operators

The manual explains here why OVERLAPS works this way:

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

Solution 3

Have not tried this but worth a shot

SELECT * 
FROM some_table 
WHERE some_date 
BETWEEN '2012-01-01' AND date('2013-04-01') - integer '1'

http://www.postgresql.org/docs/9.1/static/functions-datetime.html