Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.

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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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