Inconsistent time zone output for JSON at UTC

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

Responses

Browse pgsql-bugs by date

  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