Generate series of dates - using date type as input
generate-series, postgresql, postgresql-9.1, timestamp, types
Solution
Thanks to function type resolution we can also pass `date` values to `generate_series()` because there is an implicit cast from `date` to `timestamp` as well as from `date` to `timestamptz`. Would be ambiguous, but `timestamptz` is "preferred" among "Date/time types". Detailed explanation:
- Generating time series between two dates in PostgreSQL
For a bare `date` the local time `00:00` is assumed in the cast. Be aware that the current time zone setting directly affects the result if you use `date` as input since, obviously, '2014-01-10 00:00' represents a different point in time in Tokio than it does in New York.
How does Postgres decide what types are acceptable?
Postgres basically distinguishes between three types of casts:
`Explicit casts` .. when using `CAST` or `::` syntax. `Assignment cast` .. implicit cast when a value is assigned to a target column. `Implicit cast` .. implicit casts in all other expressions.
There has to be an implicit cast registered in the system from the input type to the expected type to make a function silently accept (and convert) an input value.
To see which casts are defined to `timestamptz`, you can query the catalog table `pg_cast`:
SELECT castsource::regtype, casttarget::regtype, castcontext
FROM pg_cast
WHERE casttarget = 'timestamptz'::regtype;
castsource | casttarget | castcontext
-----------------------------+--------------------------+-------------
abstime | timestamp with time zone | i
date | timestamp with time zone | i
timestamp without time zone | timestamp with time zone | i
timestamp with time zone | timestamp with time zone | i
All of these casts are implicit. The manual on `castcontext`:
Indicates what contexts the cast can be invoked in. `e` means only as an explicit cast (using `CAST` or `::` syntax). `a` means implicitly in assignment to a target column, as well as explicitly. `i` means implicitly in expressions, as well as the other cases.
Bold emphasis mine.
Problem
Documentation for `generate_series` says that argument can be `int` or `bigint` for `generate_series(start, stop)` and `generate_series(start, stop, step)` cases and `timestamp` or `timestamp with time zone` for `generate_series(start, stop, step interval)`. What is the reason that `generate_series` works also with `date` type as input and returns `timestamp with timezone`? ``` pg=# select generate_series('2014-01-01'::date,'2014-01-02'::date,'1 day'); generate_series ------------------------ 2014-01-01 00:00:00+01 2014-01-02 00:00:00+01 (2 rows) ```