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?

Answer 1

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.

Answer 2

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

Answer 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

Answer 4

All the answers above provide a working solution but is incomplete in one way or the other. Since I was looking for an SQL only solution (No function), I am combining the best tips from the solutions above.

The ideal solution for my question would be:

SELECT * FROM table
WHERE date >= '2012-01-01' AND date < date('2013-04-01') + interval '1 month'

EDIT

I am not using the overlap function here because I am passing the default date values for start and end as 'epoch' and 'now'. If the user has not specified any time range the query becomes:

SELECT * FROM table
WHERE date >= 'epoch' AND date < 'now'

Overlap function cannot handle 'epoch' and 'now' and gives and SQL error while the above code work perfectly for both the cases.

PS: I have upvoted all the answers that was correct in a way and led me to this solution.

Answer 5

SET search_path=tmp;

DROP TABLE zdates;
CREATE TABLE zdates
        ( zdate timestamp NOT NULL PRIMARY KEY
        , val INTEGER NOT NULL
        );
-- some data
INSERT INTO zdates(zdate,val)
SELECT s, 0
FROM generate_series('2012-01-01', '2012-12-31', '1 day'::interval ) s
        ;

UPDATE zdates
SET val = 1000 * random();

DELETE FROM zdates
WHERE random() < 0.1;

-- CTE to round the intervals down/up to the begin/end of the month
WITH zope AS (
        SELECT date_trunc('month', zdate)::date AS zbegin
        ,  date_trunc('month', zdate+interval '1 month')::date AS zend
        , val AS val
        FROM zdates
        )
SELECT z.zbegin
        , z.zend
        , COUNT(*) AS zcount
        , SUM(val) AS zval
FROM zope z
GROUP BY z.zbegin, z.zend
ORDER BY z.zbegin, z.zend
        ;

RESULT:

CREATE TABLE
INSERT 0 366
UPDATE 366
DELETE 52
   zbegin   |    zend    | zcount | zval  
------------+------------+--------+-------
 2012-01-01 | 2012-02-01 |     28 | 13740
 2012-02-01 | 2012-03-01 |     28 | 14923
 2012-03-01 | 2012-04-01 |     26 | 13775
 2012-04-01 | 2012-05-01 |     25 | 11880
 2012-05-01 | 2012-06-01 |     25 | 12693
 2012-06-01 | 2012-07-01 |     25 | 11082
 2012-07-01 | 2012-08-01 |     26 | 13254
 2012-08-01 | 2012-09-01 |     28 | 13632
 2012-09-01 | 2012-10-01 |     28 | 16461
 2012-10-01 | 2012-11-01 |     23 | 12622
 2012-11-01 | 2012-12-01 |     24 | 12554
 2012-12-01 | 2013-01-01 |     28 | 14563
(12 rows)