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 <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1wN9Om-000c2q-2l
	for pgsql-docs@arkaria.postgresql.org;
	Wed, 13 May 2026 13:13:16 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1wN9Ol-008vkh-17
	for pgsql-docs@arkaria.postgresql.org;
	Wed, 13 May 2026 13:13:15 +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 <bruce@momjian.us>)
	id 1wN9Ol-008vkZ-0H
	for pgsql-docs@lists.postgresql.org;
	Wed, 13 May 2026 13:13:15 +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 <bruce@momjian.us>)
	id 1wN9Oi-00000000P2i-2TLY
	for pgsql-docs@lists.postgresql.org;
	Wed, 13 May 2026 13:13:14 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us;
	s=2026010100; h=In-Reply-To:Content-Type:MIME-Version:References:Message-ID:
	Subject:Cc:To:From:Date:Sender:Reply-To:Content-Transfer-Encoding:Content-ID:
	Content-Description; bh=OUlgjhwyy2e6tehilg+F/9y49pMVUFnFGUi5sa55Tjo=; b=Q1L4V
	H+NmfjSfylQYmp9yI8Gv0uqxs7wIodhMO6OK53cn6DkwJPhBKKUo5bRFuIgomgz4jW+Eo4gJn8g+X
	oTyKU/tO6jxcaMRSPLQ9h2xW4vhlGGRnD67q7Bj6oYrPBKG945/tCMnvLMLpmuVBMjBbd37zhSKHi
	O2nC3BxU8AlgN+q0dBb7X1LT8vo1zKwPiQKVUGGE3QRA5PVEs8sX3inLTlWWI+xDy5cZr9IWB19/+
	YvxmxuXPvdTYm5sRm8Nfy86idXBsMf4WaQ4TUtODx0jC8r6IRsarmTgiUtQIoz423RUeHVFCm+2QH
	FWx6mHTXPhBW5Yxw5JIAZQfN8vUdg==;
Received: from bruce by momjian.us with local (Exim 4.98.2)
	(envelope-from <bruce@momjian.us>)
	id 1wN9Og-00000009hvG-2f7l;
	Wed, 13 May 2026 09:13:10 -0400
Date: Wed, 13 May 2026 09:13:10 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Richard Neill <rjn@richardneill.org>
Cc: github@richardneill.org, pgsql-docs@lists.postgresql.org
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the
 timezone.
Message-ID: <agR45nws-Y1StCCH@momjian.us>
References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
 <agPDkRGXmdyD1EcR@momjian.us>
 <ddf41f033a8add84e1f28a095defafae@richardneill.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="OHbfgx1d1Nb+6djR"
Content-Disposition: inline
In-Reply-To: <ddf41f033a8add84e1f28a095defafae@richardneill.org>
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Archived-At: 
 <https://www.postgresql.org/message-id/agR45nws-Y1StCCH%40momjian.us>
Precedence: bulk


--OHbfgx1d1Nb+6djR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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.

> So I think it deserves a "Caution" or "note" boxout, or at least to have
> words "the originally stated or assumed time zone is not retained." made in
> bold.

I have created the attached patch which is more explicit and adds an
<emphasis> tag.

> 
> 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.

> 2. How to store a general timestamp in NO timezone (for example, to store
> the concept that "Armistice Day is remembered at 11:00 on 11/11" - which is
> the same for everyone across the world, even though the underlying UTC value
> is undefined). Presumably you could store this as 2 columns (date, time),
> but there's no compound "date+time" type, and it would not be the same as
> timestamp.

Doesn't TIMESTAMP WITHOUT TIME ZONE do this?

	CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE);

	INSERT INTO test VALUES ('now');

	SHOW timezone;
	     TimeZone
	------------------
	 America/New_York
	
	SELECT * FROM test;
	             x
	----------------------------
-->	 2026-05-13 09:08:56.485716
	
	SET timezone = 'Asia/Tokyo';

	SELECT * FROM test;
	             x
	----------------------------
-->	 2026-05-13 09:08:56.485716

> 3. While the docs caution against using "time with timezone", they don't
> recommend whether to use "timestamp" or "timestamptz" as the default. Such a
> recommendation might be helpful.

Uh, I guess it depends on what the user wants.  Should we make a clear
recommendation?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

--OHbfgx1d1Nb+6djR
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..9ef044ecadb 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -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
       <xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
       offset for the <varname>timezone</varname> 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
+      <emphasis>cannot</emphasis> be retrieved later.
      </para>
 
      <para>

--OHbfgx1d1Nb+6djR--