Re: Inconsistent time zone output for JSON at UTC - Mailing list pgsql-bugs

From Gregory Jensen
Subject Re: Inconsistent time zone output for JSON at UTC
Date
Msg-id DB8PR09MB343538E43776B86B6B70EF88E0DC9@DB8PR09MB3435.eurprd09.prod.outlook.com
Whole thread Raw
In response to Re: Inconsistent time zone output for JSON at UTC  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-bugs
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> 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.

pgsql-bugs by date:

Previous
From: Michael Paquier
Date:
Subject: Re: BUG #17485: Records missing from Primary Key index when doing REINDEX INDEX CONCURRENTLY
Next
From: Tom Lane
Date:
Subject: Re: Inconsistent time zone output for JSON at UTC