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.