Getting date list in a range in PostgreSQL
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?
8 Answers Getting date list in a range in PostgreSQL
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)
a_horse_with_no_name 6 year ago
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"
Burhan Khalid 6 year ago
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
Miljen Mikic 4 year ago
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.
wildplasser 6 year ago
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
maniek 6 year ago
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!
BJury 4 year ago
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
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
Clodoaldo Neto 6 year ago
select generate_series('2012-06-29', '2012-07-03', '1 day'::interval)::date;