public inbox for [email protected]
help / color / mirror / Atom feedwhy at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
5+ messages / 4 participants
[nested] [flat]
* why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
@ 2025-04-29 02:40 H Witt <[email protected]>
0 siblings, 3 replies; 5+ messages in thread
From: H Witt @ 2025-04-29 02:40 UTC (permalink / raw)
To: [email protected] <[email protected]>
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|
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
@ 2025-04-29 03:07 Tom Lane <[email protected]>
parent: H Witt <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2025-04-29 03:07 UTC (permalink / raw)
To: H Witt <[email protected]>; +Cc: [email protected] <[email protected]>
H Witt <[email protected]> writes:
> 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'
They're different because '+08:00' is read as a POSIX time zone
specification, which has the opposite sign convention to what
you are thinking. See
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
and especially
https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html
and remember the adage "The great thing about standards is there are
so many to choose from". Sadly, with only two relevant standards
humanity has still managed to cover the design space of which
direction from Greenwich gets the positive sign.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
@ 2025-04-29 03:07 David G. Johnston <[email protected]>
parent: H Witt <[email protected]>
2 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2025-04-29 03:07 UTC (permalink / raw)
To: H Witt <[email protected]>; +Cc: [email protected] <[email protected]>
On Mon, Apr 28, 2025 at 7:40 PM H Witt <[email protected]> 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 (+
== West). When you write the latter you get the ISO convention (+ == 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names
@ 2025-04-29 03:09 Bruce Momjian <[email protected]>
parent: H Witt <[email protected]>
2 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2025-04-29 03:09 UTC (permalink / raw)
To: H Witt <[email protected]>; +Cc: [email protected] <[email protected]>
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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 5+ messages in thread
* =?gb2312?B?u9i4tDogd2h5IGF0IHRpbWUgem9uZSB0el9vZmZzZXQgbm90IGVxdWFsIHR6?= =?gb2312?B?X25hbWU/ICB0el9uYW1lIGhhcyBzYW1lIG9mZnNldCBpbiBwZ190aW1lem9u?= =?gb2312?Q?e=5Fnames?=
@ 2025-04-29 06:28 H Witt <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: H Witt @ 2025-04-29 06:28 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected] <[email protected]>
Hi all,
Thanks for explaining. I have got it.
________________________________
发件人: Bruce Momjian <[email protected]>
发送时间: 2025年4月29日 11:09
收件人: H Witt <[email protected]>
抄送: [email protected] <[email protected]>
主题: 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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-29 06:28 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-29 02:40 why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names H Witt <[email protected]>
2025-04-29 03:07 ` Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names Tom Lane <[email protected]>
2025-04-29 03:07 ` Re: why at time zone tz_offset not equal tz_name? tz_name has same offset in pg_timezone_names David G. Johnston <[email protected]>
2025-04-29 03:09 ` Bruce Momjian <[email protected]>
2025-04-29 06:28 ` =?gb2312?B?u9i4tDogd2h5IGF0IHRpbWUgem9uZSB0el9vZmZzZXQgbm90IGVxdWFsIHR6?= =?gb2312?B?X25hbWU/ICB0el9uYW1lIGhhcyBzYW1lIG9mZnNldCBpbiBwZ190aW1lem9u?= =?gb2312?Q?e=5Fnames?= H Witt <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox