Re: 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.