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:25 PM, Gary Bernhardt
> <gary(dot)bernhardt(at)gmail(dot)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.
>