Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNDe4-000ejb-2T for pgsql-docs@arkaria.postgresql.org; Wed, 13 May 2026 17:45:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNDe3-009yF3-0P for pgsql-docs@arkaria.postgresql.org; Wed, 13 May 2026 17:45:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNDe2-009yEu-2p for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 17:45:19 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wNDe0-00000000RDe-1GyO for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 17:45:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us; s=2026010100; h=In-Reply-To:Content-Transfer-Encoding:Content-Type: MIME-Version:References:Message-ID:Subject:Cc:To:From:Date:Sender:Reply-To: Content-ID:Content-Description; bh=WGlzkqsHW6Ha0n7IJ9ymYirwtudhL3z1ROVIlZskaO0=; b=b5ZFWsZC+DPiO+vzDFKn054Udx RWaFevqbRFoKilVGqLnEi84deIHUiZcI9G8edD3a6MWtVAs/vn+7k/LR0QVvSyDxs2qgcT15aVhTz 7WjKv0oA91IfKWu28ygwSjicxF6vLxhQRH3iDWNAVjVLZpo/nF85nbR6hWW2Z+QDE/UnVedLV/9+1 WltxFPd2NP9aDEknn/Jh3E9chiW9Xt9oCPAb8U/diqacxyqatCbwh4z+nwA8gi08jImvU6aHhlw04 kOjudCIkLbtG+4VoSsNPgNBAMf2YXt4KNicN9b0HiqDTkyF/xd9pt8Daril/9dTeVnG4SJFe8hBcp Ess93+hQ==; Received: from bruce by momjian.us with local (Exim 4.98.2) (envelope-from ) id 1wNDdz-0000000AlGi-07CY; Wed, 13 May 2026 13:45:15 -0400 Date: Wed, 13 May 2026 13:45:15 -0400 From: Bruce Momjian To: Kirk Parker Cc: Richard Neill , github@richardneill.org, pgsql-docs@lists.postgresql.org Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Message-ID: References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="JV5fj1MBkB38lX+g" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --JV5fj1MBkB38lX+g Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote: > On Wed, May 13, 2026 at 6:13 AM Bruce Momjian wrote: > > On Wed, May 13, 2026 at 03:21:11AM +0100, Richard Neill wrote: > > Hi Bruce, > > > > Thanks for your reply. Yes I think it does need to be stated more boldly > - > > from a "Poka-Yoke" perspective (and despite using Postgresql for years), > I > > didn't properly understand it the first time. > > > > I think it's a gotcha, because, to me, the name suggests a different > > behaviour. > > I understood "timestamp with timezone" as "a data type which stores the > > timestamp, and stores the timezone WITH (i.e. alongside) it". > > Yes, the name is confusing. > > If it could be done over, naming it "universal timestamp" or "utc timestamp" > would  be better,   But of course there is no possibility of changing it at > this late date. Yes, the SQL standard requires the syntax we currently support. > > While looking at the docs, I can also see 3 other things that would be > > helpful to add: > > > > 1. How to actually store "timestamp_and_timezone" ? (i.e. I want to store > > the UTC value, and I want to store and retrieve the original offset). > > Presumably the answer is to store both timestamptz AND the integer > tz_offset > > (is there a "timezone" datatype, or should that just be a string?) > > Yes. > > Yes, not sure why this is a difficult concept.  "A unique and universal > representation of a specific moment of terrestrial timekeeping" is what the > timestamptz stores. If you want to record the offset (or timezone region, which > is not exactly the same thing) from the user's perspective, go ahead and create > a column for that.  FWIW I can't think of a single time in over 3 decades of > building databases where I would have wanted that, but if your use case needs > it the implementation is simple and obvious. True, I have never seen this requested either. > How could we usefully make a single recommendation?  The only plausible > recommenation is "use the datatype that best suits your purposes", and if the > documentation doesn't do an adequate job of describing the differences and > applicability, that's what should be addressed, rather than asking for a > one-size-fits-all recommendation. Agreed. > And looking at the chapter again, I think table 8.9 does foster the confusion: > >     Name                                 Description >     timestamp [ without time zone ]  ==  both date and time (no time zone) >     timestamp with time zone         ==  both date and time, with time zone > > Here Description is presented generally, but in fact it only describes the i/o > presentation of the two types.  *Storage* for both types is identical (or at > least appears that way to the user). > > What about something like: "Timestamp [Without Time Zone] takes the the date/ > time verbatim without any reference to time zones, whereas Timestamp With Time > Zone converts from the specified offset (or session's time zone if no offset is > specified) to UTC for storage, and on retrieval converts to the session's time > zone".  That's fairly wordy for a table entry, but it does do a better job of > conveying what's actually happening and omits the implication that we store the > session's time zone or offset along with the UTC timestamp. I see your point. I went with adding the wording "no time zone adjustment" and "with time zone adjustment" in the table. Patch attached. You can see the output at: https://momjian.us/tmp/pgsql/datatype-datetime.html -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. --JV5fj1MBkB38lX+g Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="time_zone.diff" diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index d8d91678e86..959b443a72e 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -252,25 +252,25 @@ time [ (p) ] [ without time zone ] - time of day (no time zone) + time of day (no time zone adjustment) time [ (p) ] with time zone timetz - time of day, including time zone + time of day, including time zone adjustment timestamp [ (p) ] [ without time zone ] - date and time (no time zone) + date and time (no time zone adjustment) timestamp [ (p) ] with time zone timestamptz - date and time, including time zone + date and time, including time zone adjustment @@ -1768,7 +1768,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea; timestamp [ (p) ] [ without time zone ] 8 bytes - both date and time (no time zone) + both date and time (no time zone adjustment) 4713 BC 294276 AD 1 microsecond @@ -1776,7 +1776,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea; timestamp [ (p) ] with time zone 8 bytes - both date and time, with time zone + both date and time, with time zone adjustment 4713 BC 294276 AD 1 microsecond @@ -1800,7 +1800,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea; time [ (p) ] with time zone 12 bytes - time of day (no date), with time zone + time of day (no date), with time zone adjustment 00:00:00+1559 24:00:00-1559 @@ -2263,8 +2263,9 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' then it is assumed to be in the time zone indicated by the system's parameter, and is converted to UTC using the offset for the timezone zone. - In either case, the value is stored internally as UTC, and the - originally stated or assumed time zone is not retained. + In either case, the value is stored internally as UTC. The + originally stated or assumed time zone is not retained and + cannot be retrieved later. --JV5fj1MBkB38lX+g--