Re: Unexpected casts while using date_trunc()

Chris Bandy <bandy(dot)chris(at)gmail(dot)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