From: | Gregory Jensen <GreatDJonfire(at)hotmail(dot)co(dot)uk> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Inconsistent time zone output for JSON at UTC |
Date: | 2022-05-31 10:34:51 |
Message-ID: | DB8PR09MB343509A64178D35FEC961753E0DC9@DB8PR09MB3435.eurprd09.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2022-05-31 11:51:07 | Re: psql 15beta1 does not print notices on the console until transaction completes |
Previous Message | Giorgio Saviane | 2022-05-31 09:26:17 | Re: BUG #17449: Disk space not released |