Re: Incorrect/confusing information about 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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.