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@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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