Converting to UTC multiple times converts back to local time zone
now() is local, as I expect:
testdb=# select now();
2018-06-25 16:41:28.037072-07
And converting to UTC does convert to UTC:
testdb=# select now() at time zone 'utc';
2018-06-25 23:41:23.700795
But converting that timestamp to UTC a second time converts back to
local:
testdb=# select (now() at time zone 'utc') at time zone 'utc';
2018-06-25 16:43:03.200762-07
This seems to happen regardless of where the UTC timestamp comes from.
Here's the same thing done with a subquery:
testdb=# select (ts at time zone 'utc') from (select now() at time zone
'utc' as ts) as t1;2018-06-25 16:44:05.219322-07
This seems very wrong to me. But this also seems like something that
would have been exercised many, many times in the wild.
I'd expect "converting" a UTC timestamp to UTC would keep it in UTC. Am
I missing something?
On Mon, Jun 25, 2018 at 4:47 PM, Gary Bernhardt <gary.bernhardt@gmail.com>
wrote:
testdb=# select (now() at time zone 'utc') at time zone 'utc';
2018-06-25 16:43:03.200762-07
I'd expect "converting" a UTC timestamp to UTC would keep it in UTC. Am I
missing something?
Not a bug - the behavior is documented here:
https://www.postgresql.org/docs/9.6/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
Specifically, for the point-in-time types whenever one type is deferenced
using AT TIME ZONE the alternate type is output.
SELECT pg_typeof(now()); -- timestamp with time zone (timestamptz)
SELECT pg_typeof(now() at time zone 'utc'); -- timestamp without time zone
(timestamp)
I'll agree that this was a surprising finding for me too, but in the
overall scheme of how PostgreSQL works, and other features it provides
(e.g., TimeZone GUC and to_char()) it fits.
David J.
P.S. All timestamptz values are stored at UTC without knowledge of the
original timezone.
On Mon, Jun 25, 2018, at 5:20 PM, David G. Johnston wrote:
I'll agree that this was a surprising finding for me too, but in the overall scheme of how PostgreSQL works, and other features it provides (e.g., TimeZone GUC and to_char()) it fits.
Thanks, David. I find this behavior quite off-putting, but I'm happy to take your word that it's intended and consistent with other features.
On Mon, Jun 25, 2018 at 5:25 PM, Gary Bernhardt <gary.bernhardt@gmail.com>
wrote:
On Mon, Jun 25, 2018, at 5:20 PM, David G. Johnston wrote:
I'll agree that this was a surprising finding for me too, but in the
overall scheme of how PostgreSQL works, and other features it provides
(e.g., TimeZone GUC and to_char()) it fits.Thanks, David. I find this behavior quite off-putting, but I'm happy to
take your word that it's intended and consistent with other features.
Care to be more specific? You didn't really provide an example that gives
others insight into why you would use "AT TIME ZONE" twice in the same
expression.
David J.
The last example in my original email showed this happening where one
"AT TIME ZONE" is in a subquery and the other is in the outer query.
Imagine that scaled up to a large, complex query; and imagine that it
eventually grows a redundant "AT TIME ZONE" because someone modifying an
outer query doesn't realize that a deep subquery is already doing "AT
TIME ZONE". Now the time is suddenly in the wrong zone, but the
programmer explicitly asked for the timestamp to be in UTC.
We can certainly call that a mistake in the query (it's redundant if
nothing else). But I would never expect the value to switch back to
local time when I add "AT TIME ZONE 'utc'".
Show quoted text
On Mon, Jun 25, 2018, at 5:32 PM, David G. Johnston wrote:
On Mon, Jun 25, 2018 at 5:25 PM, Gary Bernhardt
<gary.bernhardt@gmail.com> wrote:>> On Mon, Jun 25, 2018, at 5:20 PM, David G. Johnston wrote:I'll agree that this was a surprising finding for me too, but in
the overall scheme of how PostgreSQL works, and other features it
provides (e.g., TimeZone GUC and to_char()) it fits.Thanks, David. I find this behavior quite off-putting, but I'm happy
to take your word that it's intended and consistent with other
features.>Care to be more specific? You didn't really provide an example that
gives others insight into why you would use "AT TIME ZONE" twice in
the same expression.>
David J.
Gary Bernhardt <gary.bernhardt@gmail.com> writes:
... But I would never expect the value to switch back to
local time when I add "AT TIME ZONE 'utc'".
It might help to understand that there's a difference between what
is stored and what is displayed. For type timestamptz, what is
stored is an absolute point in time --- effectively "in UTC", though
I'm not sure that's the best way to think about it. But for display
purposes, it's rotated into your session TimeZone setting. That's
why an AT TIME ZONE conversion might appear to produce no change ---
the display conversion reverses what AT TIME ZONE did.
regards, tom lane
On Mon, Jun 25, 2018 at 5:40 PM, Gary Bernhardt <gary.bernhardt@gmail.com>
wrote:
The last example in my original email showed this happening where one "AT
TIME ZONE" is in a subquery and the other is in the outer query. Imagine
that scaled up to a large, complex query; and imagine that it eventually
grows a redundant "AT TIME ZONE" because someone modifying an outer query
doesn't realize that a deep subquery is already doing "AT TIME ZONE". Now
the time is suddenly in the wrong zone, but the programmer explicitly asked
for the timestamp to be in UTC.We can certainly call that a mistake in the query (it's redundant if
nothing else). But I would never expect the value to switch back to local
time when I add "AT TIME ZONE 'utc'".
Well, as demonstrated its not redundant, its not doing what you believe it
should: which is that timestamptz AT TIME ZONE 'UTC' be idompotent - but
since the time zone provided can be any timezone that is not realistic.
Frankly, AT TIME ZONE is useful to taking user input and appending a known
time zone, that doesn't match the session TimeZone GUC, to it in order to
create a timestamptz value. timestamptz AT TIME ZONE's usefulness is
marginal at best - and in any case should not be used in subqueries. Pass
the original timestamptz typed value around until you are ready to send it
to the user. The query that operates at the edge can use AT TIME ZONE if
desired though I'd suggest that "to_char()" is less likely to trip people
up even if it is a bit more verbose.
That said, as your example shows, as long as both the AT TIME ZONE targets
are UTC the round-trip property holds.
David J.
On Mon, Jun 25, 2018 at 5:52 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
timestamptz AT TIME ZONE's usefulness is marginal at best - and in any
case should not be used in subqueries. Pass the original timestamptz typed
value around until you are ready to send it to the user.
Just to clarify that a bit - usually query results as passed to another
layer which then presents the results to a person. Pass that layer a
timestamptz and let it decide how to deal with presentation. If psql is
used as the client presentation layer then you will need to use AT TIME
ZONE (or TimeZone GUC depending on the situation) but that still falls
within the general idea of pushing display as close to the user as possible.
David J.