回复: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names - Mailing list 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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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