Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9bKm-003sSr-D2 for pgsql-novice@arkaria.postgresql.org; Tue, 29 Apr 2025 03:08:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u9bKk-004kSh-AH for pgsql-novice@arkaria.postgresql.org; Tue, 29 Apr 2025 03:08:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9bKk-004kSQ-1f for pgsql-novice@lists.postgresql.org; Tue, 29 Apr 2025 03:08:35 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9bKi-00072U-2m for pgsql-novice@lists.postgresql.org; Tue, 29 Apr 2025 03:08:34 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-2d0d25cebfeso3789810fac.2 for ; Mon, 28 Apr 2025 20:08:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745896110; x=1746500910; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=mcev66h7rQg9TXbhptAFrpNtf+Dw62sFfzvLO5QxsMw=; b=SR8JaO5WpnxGXsODx6oBeZjhKbBjy/qMVKW4x6mz7ON8HgP5ACF5YIHZ9GeBBw/BB2 aCXg5hSbCBLkEGfAnkjlqXj7gVXz4QFucdvmtoOmgQlny3gsbX370iH+EjksIQEiIfX9 vSpN8H+TnYJqnEut4D+IpNko9/HWcJ6esyb1GbcWLzlAnNqNoFe8D6STfqw353f9Hbpr 2MGVIyDu5hJp4nmnURNxmBxcbiB/mU/xNZ9Yq4RaUmC+p1N7Vhp8Ela7tqtnj1Q9IcaP F2mkbsIdyZnkHZaQ7JGuFVx+6Va+amRBS+yod9pCHJlWENa+9a5r+CXtoq2fDY6b5gFh xzUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745896110; x=1746500910; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=mcev66h7rQg9TXbhptAFrpNtf+Dw62sFfzvLO5QxsMw=; b=AcUo12u3aZ33+EhJLcKHKKcCr5XWMrElh/8e31xvz3ZnBPFea3/xfA/4Bkx2yBJIeF MtRTwK5f3u9byZtfHUUsI78jlpVERHVIIj+MUv9+PMfNJJizY5q0L1QGRvwV4T6iWZtS re0huaC734zokGNav8g8xqHexL5/E/Jqkks5McKtjMMl9jLucrXDPplGS2A/zfpsGKsl EA3PawG/FZBqUM5FAD+roVXn/UetTxdDI9l+ZsxuTtPbij+bmIn+Fi8OUSQI3EqLn6lF bUZAxuc7USR9xP2Wx2f9aCBJoY5UipjgA66nhVqNL3sIDinZhBC2K07D38+j0FXcVogl JAgQ== X-Gm-Message-State: AOJu0YxFhh/+sxsXmHcveMJzNXwDNtQEyQtKJyF1+DJ+7LLGw2o3G1cA 9ANOoq2hpEVAzlYiubMjfR7gZskxsJtB3rrjbeAAMf+cdvGI/TsFvNvij6rCYd2ltzOEmVHC9sf EdOZul53InkpVW+/samBsab1na4Y= X-Gm-Gg: ASbGncvwyNWDC6DRa2KI3JwRtn0US07FkVe9Zc1Pp4AyW57ceDSSrQijB3bRx5NZpE1 0+ell/ajP4zQu4AYute/wiZkp6u2d36b1RQPGY1CQsmqmmgUvOKz0mxg6fkkdaVQSHgIYzGjZuV zTumAReh4frGnPV8JKKIqu+62Yaj0rb00= X-Google-Smtp-Source: AGHT+IGVA3XQ2vJ8cj+yFt9oUlf7cErnY3xvlxKZEe37i2Kf06QDs7fE8EWeH4j4uQBaP9qXTpl5T9QvFDl9NybjkUs= X-Received: by 2002:a05:6870:82ab:b0:2d8:957a:5165 with SMTP id 586e51a60fabf-2da3ffb66c0mr997362fac.17.1745896110483; Mon, 28 Apr 2025 20:08:30 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Mon, 28 Apr 2025 20:07:54 -0700 X-Gm-Features: ATxdqUE97AjyjxgwMJrIF2aNQ5EMmsoXKjQcyK_B0WWKBG7yciifj9kSiRuyAPQ Message-ID: Subject: Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names To: H Witt Cc: "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000007aedcf0633e21d57" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007aedcf0633e21d57 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 28, 2025 at 7:40=E2=80=AFPM 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| > When you write the former you get the POSIX convention for sign meaning (+ =3D=3D West). When you write the latter you get the ISO convention (+ =3D= =3D 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. --0000000000007aedcf0633e21d57 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 28, 2025 at 7:40=E2=80=AFPM H Witt <i5d@live.com> wrote:
select=C2=A0
'2025-02-03 15:04:05'::timestamptz at time = zone=C2=A0'+08:00',
'2025-02-03 15:04:05'::timestamptz at time = zone=C2=A0'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 conventi= on for sign meaning (+ =3D=3D West).=C2=A0 When you write the latter you ge= t the ISO convention (+ =3D=3D 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 abbreviatio= ns=C2=A0for good measure)=C2=A0even exist.

David J.

--0000000000007aedcf0633e21d57--