pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedIncorrect/confusing information about timetz
5+ messages / 3 participants
[nested] [flat]
* Incorrect/confusing information about timetz
@ 2023-05-19 18:03 PG Doc comments form <noreply@postgresql.org>
2023-09-07 20:33 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: PG Doc comments form @ 2023-05-19 18:03 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: davecramer@postgres.rocks
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect/confusing information about timetz
2023-05-19 18:03 Incorrect/confusing information about timetz PG Doc comments form <noreply@postgresql.org>
@ 2023-09-07 20:33 ` Bruce Momjian <bruce@momjian.us>
2023-09-07 20:41 ` Re: Incorrect/confusing information about timetz Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2023-09-07 20:33 UTC (permalink / raw)
To: davecramer@postgres.rocks; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect/confusing information about timetz
2023-05-19 18:03 Incorrect/confusing information about timetz PG Doc comments form <noreply@postgresql.org>
2023-09-07 20:33 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
@ 2023-09-07 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-07 21:00 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-09-07 20:41 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: davecramer@postgres.rocks; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect/confusing information about timetz
2023-05-19 18:03 Incorrect/confusing information about timetz PG Doc comments form <noreply@postgresql.org>
2023-09-07 20:33 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
2023-09-07 20:41 ` Re: Incorrect/confusing information about timetz Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-07 21:00 ` Bruce Momjian <bruce@momjian.us>
2023-09-26 23:24 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2023-09-07 21:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: davecramer@postgres.rocks; pgsql-docs@lists.postgresql.org
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.
Attachments:
[text/x-diff] timetz.diff (673B, ../../ZPo6C2s9PDAwfKeS@momjian.us/2-timetz.diff)
download | inline diff:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 8d32a8c9c5..240d4875d2 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2040,7 +2040,8 @@ MINUTE TO SECOND
<literal>America/New_York</literal>. In this case specifying the date
is required in order to determine whether standard or daylight-savings
time applies. The appropriate time zone offset is recorded in the
- <type>time with time zone</type> value.
+ <type>time with time zone</type> value and is output as stored;
+ it is not adjusted to the active time zone.
</para>
<table id="datatype-datetime-time-table">
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Incorrect/confusing information about timetz
2023-05-19 18:03 Incorrect/confusing information about timetz PG Doc comments form <noreply@postgresql.org>
2023-09-07 20:33 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
2023-09-07 20:41 ` Re: Incorrect/confusing information about timetz Tom Lane <tgl@sss.pgh.pa.us>
2023-09-07 21:00 ` Re: Incorrect/confusing information about timetz Bruce Momjian <bruce@momjian.us>
@ 2023-09-26 23:24 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2023-09-26 23:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: davecramer@postgres.rocks; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-09-26 23:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-19 18:03 Incorrect/confusing information about timetz PG Doc comments form <noreply@postgresql.org>
2023-09-07 20:33 ` Bruce Momjian <bruce@momjian.us>
2023-09-07 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-07 21:00 ` Bruce Momjian <bruce@momjian.us>
2023-09-26 23:24 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox