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 1wNZ6K-000uM6-0E for pgsql-docs@arkaria.postgresql.org; Thu, 14 May 2026 16:39:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNZ6I-00DsMz-2P for pgsql-docs@arkaria.postgresql.org; Thu, 14 May 2026 16:39:54 +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 1wNZ6I-00DsMm-1i for pgsql-docs@lists.postgresql.org; Thu, 14 May 2026 16:39:54 +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 1wNZ6G-00000000bqX-1gix for pgsql-docs@lists.postgresql.org; Thu, 14 May 2026 16:39:54 +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=A1pLSuyEBf0TtoF1muCdZcLGx6Zpm71HNnOa06YWSsc=; b=us4tNoQmNfwtUNqw9QDL3Wniyj uVMqAMnlhVlXCMnRMMeJKkyFY4BJ+R4KqPxmtWGLFugrG4NiAg6fzoScjlI37+zzlCUJdpSe23DE/ 1ayDAaYaquUwxrHUxgmyvBGVpsmZMof6pfdnanYH9Tw5SaF1ZffWZ7yRuqf8xPASH++2kkGcND2ak o40wXNuTuDqL1Q3D9gXip6Q/ieXhpPd7ljAz2hs0TtAB1IKvfIPgUIqms1IJo51WHI85WQ5rfSGa1 m/reW+3G4vYfJ7xbPak3RvxqsseJnahJiLAWyViEOksPSxshRnahDpcqrjGGLPSpRYJABDBF2Kogw oCehgLfw==; Received: from bruce by momjian.us with local (Exim 4.98.2) (envelope-from ) id 1wNZ6C-0000000G2bZ-1ebn; Thu, 14 May 2026 12:39:48 -0400 Date: Thu, 14 May 2026 12:39:48 -0400 From: Bruce Momjian To: Dave Cramer Cc: Robert Treat , Kirk Parker , 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="LXEgMQYOp9lMOpz6" 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 --LXEgMQYOp9lMOpz6 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Thu, May 14, 2026 at 08:17:36AM -0400, Dave Cramer wrote: > > > On Thu, 14 May 2026 at 08:05, Bruce Momjian wrote: > > On Thu, May 14, 2026 at 07:57:21AM -0400, Robert Treat wrote: > > > 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: > > > > The quibble I have with the term "time zone adjustment" is that it > > implies there will be some adjustment, but while you can change your > > timezone preferences to display a timestamptz value in whatever time > > zone you want, the value itself is never adjusted; it is stored as a > > universal moment in time that doesn't change, regardless of the window > > dressing around it. For this reason I have taken to describing that > > difference between the two as that timestamptz stores the date and > > time with timezone awareness, and timestamp is date and time with no > > timezone awareness. Perhaps that might work better here as well? > > I used "adjustment" because the value is adjusted on input, and adjusted > on output.  I would say the value is not "aware" of the time zone > because once it is internally stored as UTC it is no longer aware of the > original time zone. > > > Adjusted seems like the wrong word. I think the correct word is converted. Okay, here is the patch with "conversion". -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future. --LXEgMQYOp9lMOpz6 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..9a3b8cfbc1d 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 conversion) time [ (p) ] with time zone timetz - time of day, including time zone + time of day, including time zone conversion timestamp [ (p) ] [ without time zone ] - date and time (no time zone) + date and time (no time zone conversion) timestamp [ (p) ] with time zone timestamptz - date and time, including time zone + date and time, including time zone conversion @@ -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 conversion) 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 conversion 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 conversion 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. --LXEgMQYOp9lMOpz6--