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

Hi all,
Thanks for explaining. I have got it.

________________________________
发件人: Bruce Momjian <bruce(at)momjian(dot)us>
发送时间: 2025年4月29日 11:09
收件人: H Witt <i5d(at)live(dot)com>
抄送: pgsql-novice(at)lists(dot)postgresql(dot)org <pgsql-novice(at)lists(dot)postgresql(dot)org>
主题: Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names

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.