[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: Inconsistent time zone output for JSON at UTC
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2022-05-31 14:26:15
Message-ID: 172786.1654007175 () sss ! pgh ! pa ! us
[Download RAW message or body]

"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


[prev in list] [next in list] [prev in thread] [next in thread]