Обсуждение: Incorrect/confusing information about timetz
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/15/datatype-datetime.html Description: This statement in section 8.5.3 states "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client." Is not correct for timetz
On Fri, May 19, 2023 at 06:03:43PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/datatype-datetime.html > Description: > > This statement in section 8.5.3 states > "All timezone-aware dates and times are stored internally in UTC. They are > converted to local time in the zone specified by the TimeZone configuration > parameter before being displayed to the client." > Is not correct for timetz Uh, yes to the storage part, no to the output part. ;-) Postgres does store the timetz time zone offset, but it doesn't adust it once it is stored so doesn't adjust for the session time zone: SHOW timezone; TimeZone ------------------ America/New_York CREATE TABLE test (x1 time, x2 timetz, x3 timestamp with time zone); INSERT INTO test VALUES ('09:00:00', '09:00:00', '2023-09-07 09:00:00'); SELECT * FROM test; x1 | x2 | x3 ----------+-------------+------------------------ 09:00:00 | 09:00:00-04 | 2023-09-07 09:00:00-04 SET TIMEZONE = 'Asia/Tokyo'; SELECT * FROM test; x1 | x2 | x3 ----------+-------------+------------------------ 09:00:00 | 09:00:00-04 | 2023-09-07 22:00:00+09 -- -- --- -- --- You can see it stored _internally_ here: ./src/include/utils/date.h typedef int64 TimeADT; typedef struct { TimeADT time; /* all time units other than months and years */ int32 zone; /* numeric time zone, in seconds */ } TimeTzADT; Do we want to document this? -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Bruce Momjian <bruce@momjian.us> writes: > Uh, yes to the storage part, no to the output part. ;-) Postgres does > store the timetz time zone offset, but it doesn't adust it once it is > stored so doesn't adjust for the session time zone: Right, it just stores a numeric UTC offset. > Do we want to document this? Section 8.5.1.2. Times already says "The appropriate time zone offset is recorded in the time with time zone value." Maybe that could be made a little more precise, say "The resolved numeric offset from UTC is recorded in the time with time zone value." regards, tom lane
On Thu, Sep 7, 2023 at 04:41:48PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Uh, yes to the storage part, no to the output part. ;-) Postgres does > > store the timetz time zone offset, but it doesn't adust it once it is > > stored so doesn't adjust for the session time zone: > > Right, it just stores a numeric UTC offset. > > > Do we want to document this? > > Section 8.5.1.2. Times already says "The appropriate time zone offset > is recorded in the time with time zone value." Maybe that could be > made a little more precise, say "The resolved numeric offset from UTC > is recorded in the time with time zone value." Yeah, there is no mention of it odd output behavior. Here is a patch to add that. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Вложения
On Thu, Sep 7, 2023 at 05:00:59PM -0400, Bruce Momjian wrote: > On Thu, Sep 7, 2023 at 04:41:48PM -0400, Tom Lane wrote: > > Bruce Momjian <bruce@momjian.us> writes: > > > Uh, yes to the storage part, no to the output part. ;-) Postgres does > > > store the timetz time zone offset, but it doesn't adust it once it is > > > stored so doesn't adjust for the session time zone: > > > > Right, it just stores a numeric UTC offset. > > > > > Do we want to document this? > > > > Section 8.5.1.2. Times already says "The appropriate time zone offset > > is recorded in the time with time zone value." Maybe that could be > > made a little more precise, say "The resolved numeric offset from UTC > > is recorded in the time with time zone value." > > Yeah, there is no mention of it odd output behavior. Here is a patch to > add that. Patch applied back to Postgres 11. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.