From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Gregory Jensen <GreatDJonfire@hotmail.co.uk>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Inconsistent time zone output for JSON at UTC
In-reply-to: 
 <CAKFQuwaDa9R9SKhKS8t4RsKto0rXK3EHjbcv6QUEso3oeLzL_w@mail.gmail.com>
References: 
 <DB8PR09MB343509A64178D35FEC961753E0DC9@DB8PR09MB3435.eurprd09.prod.outlook.com>
 <CAKFQuwaDa9R9SKhKS8t4RsKto0rXK3EHjbcv6QUEso3oeLzL_w@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 31 May 2022 05:00:34 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <172785.1654007175.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 31 May 2022 10:26:15 -0400
Message-ID: <172786.1654007175@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/172786.1654007175%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> This is the solution.  The timezone setting isn't a =E2=80=9Cserver sett=
ing=E2=80=9D, 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=E2=80=99t produce any time zone offset.

The use of JSON has nothing whatever to do with this; you're just getting
an equivalent of the string representation of the timestamp.  It is
modified to fit some ISO format spec or other, but it's the same data:

regression=3D# show timezone;
     TimeZone     =

------------------
 America/New_York
(1 row)

regression=3D# select now(), to_json(now());
              now              |              to_json               =

-------------------------------+------------------------------------
 2022-05-31 10:22:00.413512-04 | "2022-05-31T10:22:00.413512-04:00"
(1 row)

regression=3D# set timezone to 'UTC';
SET
regression=3D# select now(), to_json(now());
              now              |              to_json               =

-------------------------------+------------------------------------
 2022-05-31 14:22:03.559057+00 | "2022-05-31T14:22:03.559057+00:00"
(1 row)

If you use timestamp-without-time-zone, you get something like

regression=3D# select localtimestamp, to_json(localtimestamp);
       localtimestamp       |           to_json            =

----------------------------+------------------------------
 2022-05-31 14:24:34.927072 | "2022-05-31T14:24:34.927072"
(1 row)

			regards, tom lane