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