Re: Converting to UTC multiple times converts back to local time zone

On Mon, Jun 25, 2018 at 4:47 PM, Gary Bernhardt <gary(dot)bernhardt(at)gmail(dot)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.