From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Bandy <bandy(dot)chris(at)gmail(dot)com> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Unexpected casts while using date_trunc() |
Date: | 2018-05-24 18:46:12 |
Message-ID: | 28535.1527187572@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Paolo Crosato | 2018-05-24 19:00:55 | Re: Error on vacuum: xmin before relfrozenxid |
Previous Message | Bruce Momjian | 2018-05-24 18:30:30 | Re: Should we add GUCs to allow partition pruning to be disabled? |