Re: Inconsistent time zone output for JSON at UTC

Thanks David,

The explanation of 'at time zone' explains what I'd missed there. Much appreciated!
________________________________
From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: 31 May 2022 13:00
To: Gregory Jensen <GreatDJonfire(at)hotmail(dot)co(dot)uk>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Inconsistent time zone output for JSON at UTC

On Tuesday, May 31, 2022, Gregory Jensen <GreatDJonfire(at)hotmail(dot)co(dot)uk<mailto:GreatDJonfire(at)hotmail(dot)co(dot)uk>> wrote:

When the server time zone setting is UTC - timezone = 'UTC'

select to_json(created_at::timestamptz) from test_tz;
to_json
------------------------------------
"2022-05-31T10:20:07.133799+00:00"
(1 row)

This is the solution. The timezone setting isn't a “server setting”, it is a session-local setting with an initial value based upon server configuration. Change it for the session to UTC if you for some reason must output +00.

The at time zone operator outputs a timestamp without timezone and that is why those variants don’t produce any time zone offset.

David J.