How do I generate a date series in PostgreSQL?

Question

If you're looking to generate a time series, see this question

Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only

Function Argument Type Return Type Description
generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step

So how would I go about doing this?

Answer 1

You can use generate_series for this, but be sure to explicitly cast the arguments to "timestamp without time zone" otherwise they will default to "timestamp with timezone". PostgreSQL overloads generate_series for both inputs.

Problems with timestamp with timezone

You can see the drawback here.

SET timezone = 'America/Santiago';
SELECT generate_series(date '2016-08-15', date '2016-08-15', '1 day'); 
SELECT generate_series(date '2016-08-14', date '2016-08-15', '1 day');

Both of the above return the same amount of days. You can see it again here.

SET timezone = 'America/Sao_Paulo';
SELECT generate_series(date '2016-10-16', date '2016-10-17', '1 day');
SELECT generate_series(date '2016-10-17', date '2016-10-17', '1 day');

The above shows two ranges of one day.

The reason for this behavior is that these timezones have their "DST boundary at midnight, rather than a more sensible time in the small hours"

So what does it look like to "do it right",

SELECT generate_series(
 timestamp without time zone '2016-10-16',
 timestamp without time zone '2016-10-17',
 '1 day'
);

Now you can cast to date..

SELECT d::date
FROM generate_series(
 timestamp without time zone '2016-10-16',
 timestamp without time zone '2016-10-17',
 '1 day'
) AS gs(d);

---

This question and answer was inspired by a conversation with RhodiumToad on IRC (irc://irc.freenode.net/#postgresql). He altered me to this issue and provided the solution.

Update: two potential fixes

Option 1: generate_series(date,date,interval)

Playing around, I discovered I could perhaps save the need to explicitly cast to timestamp without time zone by overload for generate_series(date,date,interval)

Here is my function,

CREATE FUNCTION generate_series( t1 date, t2 date, i interval )
RETURNS setof date
AS $$
 SELECT d::date
 FROM generate_series(
 t1::timestamp without time zone,
 t2::timestamp without time zone,
 i
 )
 AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Now I can rerun the test case above and it's no longer fishy. These two both return the same thing,

SET timezone = 'America/Santiago';
SELECT d::date
FROM generate_series(date '2016-08-15', date '2016-08-15', '1 day')
 AS gs(d);
SELECT d::date
FROM generate_series(
 timestamp without time zone '2016-08-15',
 timestamp without time zone '2016-08-15',
 '1 day'
)
 AS gs(d);

As do these two,

SELECT d::date
FROM generate_series(date '2016-08-14', date '2016-08-15', '1 day')
 AS gs(d);
SELECT d::date
FROM generate_series(
 timestamp without time zone '2016-08-14',
 timestamp without time zone '2016-08-15',
 '1 day'
)
 AS gs(d);

Option 2: generate_series(date,date,int)

Another option is to create a new function generate_series(date,date,int) however you can't have both for the reasons mentioned here. So pick one of these,

generate_series(date,date,interval)
generate_series(date,date,int)

If you want the second option, try this one:

CREATE FUNCTION generate_series( t1 date, t2 date, i int )
RETURNS setof date
AS $$
 SELECT d::date
 FROM generate_series(
 t1::timestamp without time zone,
 t2::timestamp without time zone,
 i * interval '1 day'
 )
 AS gs(d)
$$
LANGUAGE sql
IMMUTABLE;

Caveats

With review on irc, there are some problems with those ideas,

< johto> generate_series(date, date, unknown) already works today. when you don't break it outright with the int version (e.g. generate_series(date, date, '1 day')) you change the return type from timestamptz to date. (date, date, interval) would break fewer cases, but you'd still change the output type. (it's also not obvious what should happen with (date, date, '1 hour') that currently "works" just fine)

• Erwin Brandstetter: I wrote a closely related answer on SO yesterday with details about function type resolution: stackoverflow.com/a/46499873/939860. I saw this just now and added a link since your DST demo is insightful.