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

From: Bruce Momjian <bruce(at)momjian(dot)us>
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:09:38
Message-ID: aBBC8vE-nlF79BFk@momjian.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Tue, Apr 29, 2025 at 02:40:45AM +0000, H Witt 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|

The sign is wrong in the first column:

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'
;
timezone | timezone
---------------------+---------------------
2025-02-04 04:04:05 | 2025-02-04 04:04:05

See:

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

The offset fields specify the hours, and optionally minutes and
seconds, difference from UTC. They have the format hh[:mm[:ss]]
optionally with a leading sign (+ or -). The positive sign is used
for zones west of Greenwich. (Note that this is the opposite of
the ISO-8601 sign convention used elsewhere in PostgreSQL.) hh
can have one or two digits; mm and ss (if used) must have two.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message H Witt 2025-04-29 06:28:05 回复: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
Previous Message David G. Johnston 2025-04-29 03:07:54 Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names