Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: H Witt <i5d(at)live(dot)com>
Cc: "pgsql-novice(at)lists(dot)postgresql(dot)org" <pgsql-novice(at)lists(dot)postgresql(dot)org>
Subject: Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
Date: 2025-04-29 03:07:54
Message-ID: CAKFQuwZgkaqZK5QNPceV090A4FW-PU384jbq_Tm6nt34-qeXYw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Mon, Apr 28, 2025 at 7:40 PM H Witt <i5d(at)live(dot)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.

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Bruce Momjian 2025-04-29 03:09:38 Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
Previous Message Tom Lane 2025-04-29 03:07:35 Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names