pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedInconsistent time zone output for JSON at UTC
4+ messages / 4 participants
[nested] [flat]
* Inconsistent time zone output for JSON at UTC
@ 2022-05-31 10:34 Gregory Jensen <GreatDJonfire@hotmail.co.uk>
2022-05-31 12:00 ` Re: Inconsistent time zone output for JSON at UTC David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Jensen @ 2022-05-31 10:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inconsistent time zone output for JSON at UTC
2022-05-31 10:34 Inconsistent time zone output for JSON at UTC Gregory Jensen <GreatDJonfire@hotmail.co.uk>
@ 2022-05-31 12:00 ` David G. Johnston <david.g.johnston@gmail.com>
2022-05-31 12:44 ` Re: Inconsistent time zone output for JSON at UTC Gregory Jensen <greatdjonfire@hotmail.co.uk>
2022-05-31 14:26 ` Re: Inconsistent time zone output for JSON at UTC Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: David G. Johnston @ 2022-05-31 12:00 UTC (permalink / raw)
To: Gregory Jensen <GreatDJonfire@hotmail.co.uk>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inconsistent time zone output for JSON at UTC
2022-05-31 10:34 Inconsistent time zone output for JSON at UTC Gregory Jensen <GreatDJonfire@hotmail.co.uk>
2022-05-31 12:00 ` Re: Inconsistent time zone output for JSON at UTC David G. Johnston <david.g.johnston@gmail.com>
@ 2022-05-31 12:44 ` Gregory Jensen <greatdjonfire@hotmail.co.uk>
1 sibling, 0 replies; 4+ messages in thread
From: Gregory Jensen @ 2022-05-31 12:44 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inconsistent time zone output for JSON at UTC
2022-05-31 10:34 Inconsistent time zone output for JSON at UTC Gregory Jensen <GreatDJonfire@hotmail.co.uk>
2022-05-31 12:00 ` Re: Inconsistent time zone output for JSON at UTC David G. Johnston <david.g.johnston@gmail.com>
@ 2022-05-31 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-05-31 14:26 UTC (permalink / raw)
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>
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-05-31 14:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-31 10:34 Inconsistent time zone output for JSON at UTC Gregory Jensen <GreatDJonfire@hotmail.co.uk>
2022-05-31 12:00 ` David G. Johnston <david.g.johnston@gmail.com>
2022-05-31 12:44 ` Gregory Jensen <greatdjonfire@hotmail.co.uk>
2022-05-31 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
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