Getting date list in a range in PostgreSQL

Question

I'd like to get the list of days between the two dates (including them) in a PostgreSQL database. For example, if I had:

start date: 29 june 2012
end date: 3 july 2012

then the result should be:

29 june 2012
30 june 2012 
1 july 2012 
2 july 2012 
3 july 2012

What would be the best way of doing this in PostgreSQL?

Thanks.

maniek · Accepted Answer · 2012-07-09T09:21:42.963

136

select CURRENT_DATE + i 
from generate_series(date '2012-06-29'- CURRENT_DATE, 
     date '2012-07-03' - CURRENT_DATE ) i

or even shorter:

select i::date from generate_series('2012-06-29', 
  '2012-07-03', '1 day'::interval) i

edited Jul 09 '12 at 09:21

answered Jul 09 '12 at 09:05

maniek

7,087
2
20
43

This solution is especially useful when you need to manipulate the the range in a query, such as a join – SMAG Dec 04 '18 at 18:39

score 50 · Answer 2 · answered Sep 22 '12 at 10:34

As timestamp:

select generate_series('2012-06-29', '2012-07-03', '1 day'::interval);

    generate_series     
------------------------
 2012-06-29 00:00:00-03
 2012-06-30 00:00:00-03
 2012-07-01 00:00:00-03
 2012-07-02 00:00:00-03
 2012-07-03 00:00:00-03

or casted to date:

select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date;

 generate_series 
-----------------
 2012-06-29
 2012-06-30
 2012-07-01
 2012-07-02
 2012-07-03

score 8 · Answer 3 · 2012-07-09T09:08:26.070

8

This should do it:

select date '2012-06-29' + i
from generate_series(1, (select date '2012-07-3' - date '2012-06-29')) i

If you don't want to repeat the start_date in the subselect things get a bit more complicated:

with min_max (start_date, end_date) as (
   values (date '2012-06-29', date '2012-07-3')
), date_range as (
  select end_date - start_date as duration
  from min_max
)
select start_date + i
from min_max
  cross join generate_series(1, (select duration from date_range)) i;

(See maniek's answer for a much better version of the "no-repeat" problem)

edited Jul 09 '12 at 09:08

answered Jul 09 '12 at 08:12

thanks, I've tried them and it almost works. Both get all the dates but the fist day (the 29th of june). It works if you change the "1" in generated_series function for "0" – Javi Jul 09 '12 at 08:17
generate_series is a new one for me. – Burhan Khalid Jul 09 '12 at 08:21
2

simplier, without repeating: `select CURRENT_DATE + i from generate_series(date '2012-06-29'- CURRENT_DATE, date '2012-07-03' - CURRENT_DATE ) i` – maniek Jul 09 '12 at 08:36
@maniek: very nice indeed! Why don't you post that as an answer? – Jul 09 '12 at 08:52

BJury · Answer 4 · 2014-12-10T12:33:35.530

5

For things like this its generally handy to have a dates table in the system.

Just like a numbers table they can be very useful and quicker to use than generating the dates on the fly, especially when you scale up to large data sets.

Such a date table from 1900 to 2100 will be very small, so there isn't much over head in storage.

Edit: Dunno why this is getting voted down, it will probably be the best for performance. Plus it has so many other advantages. Want to link orders to a an quarters performance numbers? Its a simple link between the tables. (Order.OrderDate -> Dates.Date -> Dates.Quarter -> PerformanceTotal.Quarter) etc. Its the same for dealing with working days, like the last working day of a month, or the first Tuesday of the previous month. Like a numbers table, I'd strongly recommend them!

edited Dec 10 '14 at 12:33

answered Jul 09 '12 at 09:21

BJury

2,526
3
16
27

1

Would that series also have things like the quarter, year, month, etc? Very handy if you're generating reports on such time periods. – BJury Oct 24 '12 at 11:42
Yeah, for uneven sequences like that then calendar tables would probably be nicer than `generate_series`; I probably should have said "there's *usually* no need for calendar tables". – mu is too short Oct 24 '12 at 17:07

score 0 · Answer 5 · answered Jul 09 '12 at 08:20

0

If you already have database that you want to query:

SELECT
   TO_CHAR(date_column,'DD Mon YYYY')
FROM
   some_table
WHERE
   date_column BETWEEN '29 Jun 2012' AND '3 JUL 2012'

GROUP BY date_column
ORDER BY date_column

This will result in:

"29 Jun 2012"
"30 Jun 2012"
"01 Jul 2012"
"02 Jul 2012"
"03 Jul 2012"

answered Jul 09 '12 at 08:20

Burhan Khalid

169,990
18
245
284

no I don't have dates in any table. It's just a list of dates I need to show in a report and maybe there won't be any data at all in some days. – Javi Jul 09 '12 at 08:24

score 0 · Answer 6 · edited Feb 07 '15 at 13:22

This PLpg/SQL function would do the trick:

CREATE OR REPLACE FUNCTION getDateList(date1 date, date2 date)
RETURNS SETOF date AS
$BODY$
DECLARE
    count integer;
    lower_limit integer :=  0;
    upper_limit integer :=  date2 - date1;
BEGIN
    FOR count IN lower_limit..upper_limit LOOP
        RETURN NEXT date1 + count;
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

score 0 · Answer 7 · answered Jul 09 '12 at 08:25

If the date range should come from a table expression, you could use the following construct:

DROP TABLE tbl ;
CREATE TABLE tbl (zdate date NOT NULL );
INSERT INTO tbl(zdate) VALUES( '2012-07-01') , ('2012-07-09' );

WITH mima AS (
        SELECT MIN(zdate)::timestamp as mi
        , MAX(zdate)::timestamp as ma
        FROM tbl
        )
SELECT generate_series( mima.mi, mima.ma, '1 day':: interval):: date
FROM mima
        ;

The casts are needed because generate_series() does not take date arguments.

Getting date list in a range in PostgreSQL

7 Answers7

Linked