0

I would like to get a JSON object in Postgres that display timestamp with Australia/Sydney timezone offset (+10:00 or +11:00 during daylight saving), for example I would like json object returned with following values:

"2021-07-31T23:59:59.123456+10:00"   
"2021-01-31T23:59:59.123456+11:00"

But when I use either to_json() or to_char() the timestamp value returned is UTC with offset +00:00

select to_json(current_timestamp::timestamptz),
to_char(current_timestamp::timestamptz, 'YYYY-MM-DD"T"HH24:MI:SS:MSOF')

"2021-01-31T07:47:22.895185+00:00"
2021-01-31T07:47:22:895+00

I have tried to add "at time zone 'AEDT'" but it shifts the timestamp value and keep the offset to +00:00.

Thanks.

Peter Xu
  • 55
  • 3
  • 10
  • Unrelated, but: `current_timestamp::timestamptz` can be simplified to `current_timestamp` –  Jan 31 '21 at 08:41

1 Answers1

1

to_json formats according to the current time zone setting of the session. I suggest that you set the session time zone to Australia/Sydney first.

set time zone 'Australia/Sydney';
select to_json('2021-01-31T07:47:22.895+00'::timestamptz);

Yields 2021-01-31T18:47:22.895+11:00 which I guess is what you need.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21