Thread: 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?
Re: Converting to UTC multiple times converts back to local time zone
From
"David G. Johnston"
Date:
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:
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 otherfeatures 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 consistentwith other features.
Re: Converting to UTC multiple times converts back to local time zone
From
"David G. Johnston"
Date:
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'".
On Mon, Jun 25, 2018, at 5:32 PM, David G. Johnston 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
Re: Converting to UTC multiple times converts back to local time zone
From
"David G. Johnston"
Date:
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.
Re: Converting to UTC multiple times converts back to local time zone
From
"David G. Johnston"
Date:
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.