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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
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>
