Re: Incorrect/confusing information about timetz
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 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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: Incorrect/confusing information about timetz
On Thu, Sep 7, 2023 at 04:41:48PM -0400, Tom Lane wrote: > Bruce Momjian 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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. 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 America/New_York. 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 - time with time zone value. + time with time zone value and is output as stored; + it is not adjusted to the active time zone.
Re: Incorrect/confusing information about timetz
Bruce Momjian 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
Re: Incorrect/confusing information about timetz
On Fri, May 19, 2023 at 06:03:43PM +, 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 https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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
