From: Bruce Momjian <bruce@momjian.us>
To: davecramer@postgres.rocks
To: pgsql-docs@lists.postgresql.org
Subject: Re: Incorrect/confusing information about timetz
Date: Thu, 7 Sep 2023 16:33:37 -0400
Message-ID: <ZPozoUwxa80aarQp@momjian.us> (raw)
In-Reply-To: <168451942371.714.9173574930845904336@wrigleys.postgresql.org>
References: <168451942371.714.9173574930845904336@wrigleys.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, davecramer@postgres.rocks, pgsql-docs@lists.postgresql.org
Subject: Re: Incorrect/confusing information about timetz
In-Reply-To: <ZPozoUwxa80aarQp@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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