Chris Bandy <bandy.ch...@gmail.com> writes: > The documentation explains that DATE is first cast to TIMESTAMP. (As I > understand it, this is an immutable cast; sounds find and appropriate.) > https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC > But in my testing, the date value is actually cast to TIMESTAMPTZ:
Yeah. There are two relevant variants of date_trunc(): regression=# \df date_trunc List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------+-----------------------------+-----------------------------------+------ pg_catalog | date_trunc | interval | text, interval | func pg_catalog | date_trunc | timestamp with time zone | text, timestamp with time zone | func pg_catalog | date_trunc | timestamp without time zone | text, timestamp without time zone | func (3 rows) and since timestamptz is the preferred type in the datetime category, the parser will prefer that one over the plain-timestamp one. There's no resolution heuristic that would let it decide that timestamp without tz is a better semantic match to a "date" input. The closest we could get with the available machinery is to make date-to-timestamptz not be an implicitly available cast, which I'm afraid would break as many things as it would fix. Just for grins, I tried changing that cast to "automatic" and ran the regression tests that way. It does have the perhaps-desirable effect that date_trunc now behaves like you expected, but it also breaks one unrelated test case that would now need an explicit cast: it's a union between a date column and a timestamptz column. Maybe that's acceptable collateral damage for some future release, but it's sure not something we could back-patch. So we probably ought to change the docs here. regards, tom lane