Postgres Pro
Downloads
Home   >   mailing lists

Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names - Mailing list pgsql-novice

From David G. Johnston
Subject Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
Date
Msg-id CAKFQuwZgkaqZK5QNPceV090A4FW-PU384jbq_Tm6nt34-qeXYw@mail.gmail.com
Whole thread Raw
In response to why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names  (H Witt <i5d@live.com>)
List pgsql-novice
Tree view
On Mon, Apr 28, 2025 at 7:40 PM H Witt <i5d@live.com> wrote:
select 
'2025-02-03 15:04:05'::timestamptz at time zone '+08:00',
'2025-02-03 15:04:05'::timestamptz at time zone 'Asia/Shanghai'

display
|2025-02-02 23:04:05.000|2025-02-03 15:04:05.000|

When you write the former you get the POSIX convention for sign meaning (+ == West).  When you write the latter you get the ISO convention (+ == East)

https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

Since people expect ISO conventions you should stick to using the zone names and forget explicit offsets (and even the abbreviations for good measure) even exist.

David J.

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
Next
From: Bruce Momjian
Date:
Subject: Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names