Converting to UTC multiple times converts back to local time zone - Mailing list pgsql-bugs

From Gary Bernhardt
Subject Converting to UTC multiple times converts back to local time zone
Date
Msg-id 1529970473.36096.1420218376.3C13DF11@webmail.messagingengine.com
Whole thread Raw
Responses Re: Converting to UTC multiple times converts back to local time zone
List pgsql-bugs
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?

pgsql-bugs by date:

Previous
From: csusza
Date:
Subject: Re: Crashed libpq.dll in a multithreaded environment in case of SSLconnection + callstack
Next
From: "David G. Johnston"
Date:
Subject: Re: Converting to UTC multiple times converts back to local time zone