To generate a series of dates this is the optimal way:
SELECT t.the_day::date
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') AS t(the_day);
Additional date_trunc() is not needed. The cast to date (the_day::date) does that implicitly.
But there is also no point in casting date literals to date as input parameter. Au contraire, timestamp is the best choice. The advantage in performance is tiny, but there is no reason not to take it. And we don't needlessly involve DST (daylight saving time) rules coupled with the conversion from date to timestamp with time zone and back. See below.
Equivalent, less explicit short syntax:
SELECT the_day::date
FROM generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') the_day;
Or with the set-returning function in the SELECT list:
SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS the_day;
I avoid "day" as identifier. It's a reserved word in standard SQL. Non-reserved in Postgres, but requires the AS keyword for a column alias (never a bad idea anyways).
And I do not advise the last variant before Postgres 10 - at least not with more than one set-returning function in the same SELECT list:
(That aside, the last variant is typically fastest by a tiny margin.)
Why timestamp [without time zone]?
There are a number of overloaded variants of generate_series(). Currently (Postgres 11):
SELECT oid::regprocedure AS function_signature
, prorettype::regtype AS return_type
FROM pg_proc
where proname = 'generate_series';
function_signature | return_type
:-------------------------------------------------------------------------------- | :--------------------------
generate_series(integer,integer,integer) | integer
generate_series(integer,integer) | integer
generate_series(bigint,bigint,bigint) | bigint
generate_series(bigint,bigint) | bigint
generate_series(numeric,numeric,numeric) | numeric
generate_series(numeric,numeric) | numeric
generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone
generate_series(timestamp with time zone,timestamp with time zone,interval) | timestamp with time zone
numeric variants were added with Postgres 9.5. The relevant ones are the last two in bold taking and returning timestamp / timestamptz.
There is no variant taking or returning date. A cast is needed to get date. The call with timestamp arguments resolves to the best variant directly without descending into function type resolution rules and without additional cast for the input.
timestamp '2004-03-07' is perfectly valid, btw. The omitted time part defaults to 00:00 with ISO format.
Thanks to function type resolution we can still pass date. But that requires more work from Postgres. There is an implicit cast from date to timestamp as well as one from date to timestamptz. Would be ambiguous, but timestamptz is "preferred" among "date/time types". So the match is decided at step 4d.:
Run through all candidates and keep those that accept preferred types
(of the input data type's type category) at the most positions where
type conversion will be required. Keep all candidates if none accept
preferred types. If only one candidate remains, use it; else continue
to the next step.
In addition to the extra work in function type resolution this adds an extra cast to timestamptz - which not only adds more cost, it can also introduce problems with DST leading to unexpected results in rare cases. (DST is a moronic concept, btw, can't stress this enough.) Related:
I added demos to the fiddle showing the more expensive query plan:
fiddle
Related:
