Inconsistent time zone output for JSON at UTC
Hello,
My problem is that depending on the scenario, a query returning JSON will inconsistently return time zone information for timestamps.
Create the database and some test data:
create table test_tz (id serial, created_at timestamp);
insert into test_tz (created_at) values (NOW());
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)
When the server time zone setting has an offset (BST for me) - timezone = 'Europe/London'
select to_json(created_at::timestamptz) from test_tz;
to_json
------------------------------------
"2022-05-31T10:20:07.133799+01:00"
(1 row)
BUT with the server set to something other than UTC and with my various attempts to get a UTC time zone back with my timestamp in JSON - the time zone is being dropped.
select to_json(created_at::timestamptz at time zone 'UTC') from test_tz;
to_json
------------------------------
"2022-05-31T09:20:07.133799"
(1 row)
select to_json(created_at::timestamptz at time zone '+00:00') from test_tz;
to_json
------------------------------
"2022-05-31T09:20:07.133799"
(1 row)
select to_json(created_at::timestamptz at time zone '00:00') from test_tz;
to_json
------------------------------
"2022-05-31T09:20:07.133799"
(1 row)
This inconsistency is causing problems when trying to parse results in Golang. The version with the time zone is what's expected. if this was consistent i could just tweak the expected date format in my program but at the moment i don't seem to be able to write a query than can cope with different database configurations.
i've not been able to find from the documentation whether this is expected behaviour and i should just configure my postgresql servers consistently or is it something inconsistent that could use a fix.
Thanks,
G
On Tuesday, May 31, 2022, Gregory Jensen <GreatDJonfire@hotmail.co.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.
Thanks David,
The explanation of 'at time zone' explains what I'd missed there. Much appreciated!
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: 31 May 2022 13:00
To: 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
On Tuesday, May 31, 2022, Gregory Jensen <GreatDJonfire@hotmail.co.uk<mailto:GreatDJonfire@hotmail.co.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.
"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