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

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