From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Gregory Jensen <GreatDJonfire@hotmail.co.uk>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Inconsistent time zone output for JSON at UTC
Date: Tue, 31 May 2022 10:26:15 -0400
Message-ID: <172786.1654007175@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwaDa9R9SKhKS8t4RsKto0rXK3EHjbcv6QUEso3oeLzL_w@mail.gmail.com>
References: <DB8PR09MB343509A64178D35FEC961753E0DC9@DB8PR09MB3435.eurprd09.prod.outlook.com>
<CAKFQuwaDa9R9SKhKS8t4RsKto0rXK3EHjbcv6QUEso3oeLzL_w@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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.
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=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
regression=# 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=# set timezone to 'UTC';
SET
regression=# 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=# 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, GreatDJonfire@hotmail.co.uk, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistent time zone output for JSON at UTC
In-Reply-To: <172786.1654007175@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox