MIME-Version: 1.0
Date: Wed, 13 May 2026 03:21:11 +0100
From: Richard Neill <rjn@richardneill.org>
To: Bruce Momjian <bruce@momjian.us>
Cc: github@richardneill.org, pgsql-docs@lists.postgresql.org
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the
 timezone.
In-Reply-To: <agPDkRGXmdyD1EcR@momjian.us>
References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
 <agPDkRGXmdyD1EcR@momjian.us>
Message-ID: <ddf41f033a8add84e1f28a095defafae@richardneill.org>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ddf41f033a8add84e1f28a095defafae%40richardneill.org>
Precedence: bulk

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

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.


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?)

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.

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.


Best wishes,

Richard


On 2026-05-13 01:19, Bruce Momjian wrote:
> On Wed, Apr 22, 2026 at 11:40:49AM +0000, PG Doc comments form wrote:
>> The following documentation comment has been logged on the website:
>> 
>> Page: https://www.postgresql.org/docs/18/datatype-datetime.html
>> Description:
>> 
>> Hi,
>> 
>> Just a small bit of feedback on this page:
>> https://www.postgresql.org/docs/current/datatype-datetime.html
>> 
>> There is a gotcha for the unwary, in that it does not preserve the 
>> original
>> timezone after the data is recorded.
>> The user needs to understand that timestamp with timezone is 
>> performing
>> implicit conversion for them, but it does not save the TZ value.
>> 
>> One might, naively, expect it to do the "obvious" thing, storing the
>> original timezone alongside the timestamp, and therefore
>> allow you to query the data and discover what the timezone was when 
>> the data
>> was input.
>> (i.e. the on-disk representation of  "17th May at 4pm New York" should 
>> be
>> different from "17th May at 1pm in Los Angeles", and that a query
>> should be able to do something like  not just the UTC value in the 
>> local
>> timezone, but also the fact that this date was recorded from the 
>> perspective
>> of a New Yorker).
>> 
>> Or in other words,  one might expect that:
>> SELECT EXTRACT (timezone FROM '2026-04-16 13:00:00+09'::timestamptz);
>> should be able to tell me the "+09", regardless of my own local time.
>> 
>> May I suggest adding a "Caution" to draw greater attention to this 
>> fact, and
>> recommending an alternative if there is one.
> 
> The documentation says:
> 
> 	https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT
> 
> 	For timestamp with time zone values, an input string that
> 	includes an explicit time zone will be converted to UTC
> 	(Universal Coordinated Time) using the appropriate offset for
> 	that time zone. If no time zone is stated in the input string,
> 	then it is assumed to be in the time zone indicated by the
> 	system's TimeZone 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.
> 
> Does this need to be stated more boldly?