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)