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,