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

Reply via email to