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

From: PG Doc comments form <[email-masked]>
Date: Wed, 22 Apr 2026 11:40:49 +0000

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.

Thanks - Richard

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 12 May 2026 20:19:29 -0400

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

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

---

From: Richard Neill <[email-masked]>
Date: Wed, 13 May 2026 03:21:11 +0100

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

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 13 May 2026 09:13:10 -0400

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

Yes, the name is confusing.

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

> 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

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

  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>

---

From: Richard Neill <[email-masked]>
Date: Wed, 13 May 2026 15:59:11 +0100

Thanks for your reply,

> I have created the attached patch which is more explicit and adds an

Thanks.

> 	INSERT INTO test VALUES ('now');

No, I think doesn't. That's probably the root of the confusion.

Using "timestamp without timezone" could be interpreted as:

   "Armistice Day is at 11:00 on 11/11 in UTC"
   (and we'll intentionally overlook the formatting of this to bodge it
    for display in different timezones as if it were local)

What we actually need here is a data-type to logically represent the 
concept of "This is a local civil date/time, which explicitly does not 
have a single well-defined UTC value.

I think that such a data-type should not be able to be cast to a 
timestamptz without resulting in an error (unless it were first cast a 
string).

For example: imagine that I'm storing a calendar. I want to represent, 
in this data-type, two values:

*  "Armistice Day  is remembered at 11:00 on 11th day of 11th month"
*  The next presidential inauguration happens at noon (Eastern time) on 
20th January 2028 at noon.

If I'm in London, then Armistice Day is remembered at 11:00 am 
(local-time), but the presidential inauguration happens at 5pm (local-time).
i.e. one of the data-values should convert for display in local-time, 
while the other one is already defined in local-time and should not be 
converted.

This problem applies in many contexts:

* The start of the new year (midnight, local-time, all timezones)

* Business statistics are measured from 9am Mon-5pm Friday (worldwide)

* Daily production figures (24 hour long days, where we explicitly 
ignore the 23 or 25-hour day as the clocks go back/forward).

* Shift patterns, where a shift is always 8 hours long, always begins at 
00:00, 08:00, 16:00, and you need to be able to use generate_series() to 
get the recorded data-points over a year (i.e. we are explicitly 'lying' 
about the clocks going back/forward, rather than ignoring it).

So I actually think there are 4 data-types we need to distinguish:

1. timestamp  (a UTC value, where the user must do the timezone 
conversion if they want it)

2. timestamptz (a UTC value, where the database does the conversion 
implicitly for your configured local timezone, or explicitly if you ask)

3. timestamp_and_timezone (a struct, which contains timestamp, AND the 
timezone (or longitude) of the human who recorded it)

4. timestamp_civil (a date and time which is always local, and cannot be 
expressed as a single UTC value). This might be achievable as a compound 
of date+time.

Of these, (3) doesn't exist, and probably should, hence my original bug 
report, and (4) is commonly achieved by mis-using (1), but that's a 
logical bug because the types are incompatible.

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

Yes. I think the recommendation should probably be something like.

--- begin ---
Whether you use timestamp, or timestamptz, the underlying record will be 
stored in UTC:

* Use timestamp if you are working in UTC

* Use timestamptz if you want to always "think" in local time, and have 
the database implicitly handle the input/output conversions."

* (Mis)use timestamp if you want to work in the local civil timezones, 
where time is a purely local value, and UTC doesn't exist, e.g. "the 
working day begins at 9am" or "Data from Monday-to-Friday" where you 
have factories in different parts of the world. For example, "Breakfast 
time is 8am (wherever you are)", rather than "Breakfast time, 8am in 
your specific timezone, happened at XXX value of UTC".
i.e. you're still recording the value in UTC in the database, but 
interpreting it as if it were not.
---end--

Best wishes,

Richard

---

From: Kirk Parker <[email-masked]>
Date: Wed, 13 May 2026 08:57:54 -0700

On Wed, May 13, 2026 at 6:13 AM Bruce Momjian <[email-masked]> wrote:

> -

If it could be done over, naming it "universal timestamp" or "utc
timestamp" would  be better,   But of course there is no possibility of
changing it at this late date.

> in

Yes, not sure why this is a difficult concept.  "A unique and universal
representation of a specific moment of terrestrial timekeeping" is what the
timestamptz stores. If you want to record the offset (or timezone region,
which is not exactly the same thing) from the user's perspective, go ahead
and create a column for that.  FWIW I can't think of a single time in over
3 decades of building databases where I would have wanted that, but if your
use case needs it the implementation is simple and obvious.

A caution, though: an earlier post in this thread mentioned wanting to
record "the fact that this date was recorded from the perspective of a New
Yorker" but that is a very problematic understanding of IANA timezone
names; it's more accurately "the timezone region whose boundaries can vary
over time  and is identified by the large city 'New York'".  South Bend,
Indiana is in that same time zone and so is Eastport, Maine.

>         INSERT INTO test VALUES ('now');

How could we usefully make a single recommendation?  The only plausible
recommenation is "use the datatype that best suits your purposes", and if
the documentation doesn't do an adequate job of describing the differences
and applicability, that's what should be addressed, rather than asking for
a one-size-fits-all recommendation.

And looking at the chapter again, I think table 8.9 does foster the
confusion:

    Name                                 Description
    timestamp [ without time zone ]  ==  both date and time (no time zone)
    timestamp with time zone         ==  both date and time, with time zone

Here Description is presented generally, but in fact it only describes the
i/o presentation of the two types.  *Storage* for both types is identical
(or at least appears that way to the user).

What about something like: "Timestamp [Without Time Zone] takes the the
date/time verbatim without any reference to time zones, whereas Timestamp
With Time Zone converts from the specified offset (or session's time zone
if no offset is specified) to UTC for storage, and on retrieval converts to
the session's time zone".  That's fairly wordy for a table entry, but it
does do a better job of conveying what's actually happening and omits the
implication that we store the session's time zone or offset along with the
UTC timestamp.

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 13 May 2026 12:48:03 -0400

>    for display in different timezones as if it were local)

Ah, so you want it to be 11am in all time zones, and actually behave
like it is 11am in each time zone.  Not sure how we would even implement
that, and it sounds actually too confusing to even implement generally.

> * Business statistics are measured from 9am Mon-5pm Friday (worldwide)

Yes, I understand, but I have never heard anyone ask for this.

> * Use timestamptz if you want to always "think" in local time, and have the
> database implicitly handle the input/output conversions."
> 
> * (Mis)use timestamp if you want to work in the local civil timezones, where
> time is a purely local value, and UTC doesn't exist, e.g. "the working day
> begins at 9am" or "Data from Monday-to-Friday" where you have factories in
> different parts of the world. For example, "Breakfast time is 8am (wherever
> you are)", rather than "Breakfast time, 8am in your specific timezone,
> happened at XXX value of UTC".

Uh, that would be a lot of detail for our docs.  Maybe a blog post would
work for this.

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 13 May 2026 13:45:15 -0400

On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> If it could be done over, naming it "universal timestamp" or "utc timestamp"
> would  be better,   But of course there is no possibility of changing it at
> this late date.

Yes, the SQL standard requires the syntax we currently support.

> timestamptz stores. If you want to record the offset (or timezone region, which
> is not exactly the same thing) from the user's perspective, go ahead and create
> a column for that.  FWIW I can't think of a single time in over 3 decades of
> building databases where I would have wanted that, but if your use case needs
> it the implementation is simple and obvious.

True, I have never seen this requested either.

> recommenation is "use the datatype that best suits your purposes", and if the
> documentation doesn't do an adequate job of describing the differences and
> applicability, that's what should be addressed, rather than asking for a
> one-size-fits-all recommendation.

Agreed.

> And looking at the chapter again, I think table 8.9 does foster the confusion:
> 
>     Name                                 Description
> Here Description is presented generally, but in fact it only describes the i/o
> presentation of the two types.  *Storage* for both types is identical (or at
> least appears that way to the user).
> 
> What about something like: "Timestamp [Without Time Zone] takes the the date/
> time verbatim without any reference to time zones, whereas Timestamp With Time
> Zone converts from the specified offset (or session's time zone if no offset is
> specified) to UTC for storage, and on retrieval converts to the session's time
> zone".  That's fairly wordy for a table entry, but it does do a better job of
> conveying what's actually happening and omits the implication that we store the
> session's time zone or offset along with the UTC timestamp.

I see your point.  I went with adding the wording "no time zone
adjustment" and "with time zone adjustment" in the table.  Patch
attached.  You can see the output at:

	https://momjian.us/tmp/pgsql/datatype-datetime.html

  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..959b443a72e 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -252,25 +252,25 @@
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry></entry>
-       <entry>time of day (no time zone)</entry>
+       <entry>time of day (no time zone adjustment)</entry>
       </row>
 
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry><type>timetz</type></entry>
-       <entry>time of day, including time zone</entry>
+       <entry>time of day, including time zone adjustment</entry>
       </row>
 
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry></entry>
-       <entry>date and time (no time zone)</entry>
+       <entry>date and time (no time zone adjustment)</entry>
       </row>
 
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry><type>timestamptz</type></entry>
-       <entry>date and time, including time zone</entry>
+       <entry>date and time, including time zone adjustment</entry>
       </row>
 
       <row>
@@ -1768,7 +1768,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
        <row>
         <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
         <entry>8 bytes</entry>
-        <entry>both date and time (no time zone)</entry>
+        <entry>both date and time (no time zone adjustment)</entry>
         <entry>4713 BC</entry>
         <entry>294276 AD</entry>
         <entry>1 microsecond</entry>
@@ -1776,7 +1776,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
        <row>
         <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
         <entry>8 bytes</entry>
-        <entry>both date and time, with time zone</entry>
+        <entry>both date and time, with time zone adjustment</entry>
         <entry>4713 BC</entry>
         <entry>294276 AD</entry>
         <entry>1 microsecond</entry>
@@ -1800,7 +1800,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
        <row>
         <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
         <entry>12 bytes</entry>
-        <entry>time of day (no date), with time zone</entry>
+        <entry>time of day (no date), with time zone adjustment</entry>
         <!-- see MAX_TZDISP_HOUR in datatype/timestamp.h -->
         <entry>00:00:00+1559</entry>
         <entry>24:00:00-1559</entry>
@@ -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>

---

From: Robert Treat <[email-masked]>
Date: Thu, 14 May 2026 07:57:21 -0400

> True, I have never seen this requested either.

It is actually requested so often, there is an extension for it.
https://pgxn.org/dist/timestampandtz/

That said, I would note that most people who go down that path usually
find that it has a hard time holding up, due to general complexity and
misunderstanding of how time, dates, and timezones really work. For
example, both upthread examples of where this would apply are not
always true; new years don't always start at midnight (some lunar
calendars mark the new year based on visibility of the moon, not a
specific time like midnight; not to mention places that may operate
under multiple (ie local vs official timezones). Similarly, if you can
find a global business that will let you measure its business
statistics 9-5 m-f, well, what a way to make a living I guess.

But my point here is that if you think this is what you need, check
out the extension above.

> attached.  You can see the output at:

The quibble I have with the term "time zone adjustment" is that it
implies there will be some adjustment, but while you can change your
timezone preferences to display a timestamptz value in whatever time
zone you want, the value itself is never adjusted; it is stored as a
universal moment in time that doesn't change, regardless of the window
dressing around it. For this reason I have taken to describing that
difference between the two as that timestamptz stores the date and
time with timezone awareness, and timestamp is date and time with no
timezone awareness. Perhaps that might work better here as well?

Robert Treat
https://xzilla.net

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 14 May 2026 08:05:23 -0400

> timezone awareness. Perhaps that might work better here as well?

I used "adjustment" because the value is adjusted on input, and adjusted
on output.  I would say the value is not "aware" of the time zone
because once it is internally stored as UTC it is no longer aware of the
original time zone.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 14 May 2026 09:35:19 -0700

>    for display in different timezones as if it were local)

Sure, but that isn’t how it is documented.  Timestamp without timezone has
no timezone context so interpreting it as “in utc” is not justified by the
docs.

> 1. timestamp  (a UTC value, where the user must do the timezone conversion
> if they want it)

Doesn’t exist, don’t see it being added.

> implicitly for your configured local timezone, or explicitly if you ask)

Done.

> timezone (or longitude) of the human who recorded it)

Not in core.

> expressed as a single UTC value). This might be achievable as a compound of
> date+time.

This is what already exists in timestamp.

> report, and (4) is commonly achieved by mis-using (1), but that's a logical
> bug because the types are incompatible.

A valid perspective but not one shared by the project.  The absence of 3 is
not a bug.

> Yes. I think the recommendation should probably be something like.

I’m not against examples of common usages and documenting gotchas but they
have to be in line with the design we have.

As you noted, however, both timestamp and timestamptz exist because data in
the world fall into the two categories - local time and point-in-time.

David J.

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 14 May 2026 12:39:48 -0400

On Thu, May 14, 2026 at 08:17:36AM -0400, Dave Cramer wrote:
> 
> 
> On Thu, 14 May 2026 at 08:05, Bruce Momjian <[email-masked]> wrote:
> 
> Adjusted seems like the wrong word. I think the correct word is converted.

Okay, here is the patch with "conversion".

  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..9a3b8cfbc1d 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -252,25 +252,25 @@
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry></entry>
-       <entry>time of day (no time zone)</entry>
+       <entry>time of day (no time zone conversion)</entry>
       </row>
 
       <row>
        <entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry><type>timetz</type></entry>
-       <entry>time of day, including time zone</entry>
+       <entry>time of day, including time zone conversion</entry>
       </row>
 
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
        <entry></entry>
-       <entry>date and time (no time zone)</entry>
+       <entry>date and time (no time zone conversion)</entry>
       </row>
 
       <row>
        <entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
        <entry><type>timestamptz</type></entry>
-       <entry>date and time, including time zone</entry>
+       <entry>date and time, including time zone conversion</entry>
       </row>
 
       <row>
+        <entry>both date and time (no time zone conversion)</entry>
+        <entry>both date and time, with time zone conversion</entry>
+        <entry>time of day (no date), with time zone conversion</entry>
 
      <para>

---

From: Tom Lane <[email-masked]>
Date: Thu, 14 May 2026 14:02:15 -0400

> Okay, here is the patch with "conversion".

timetz does store a UTC offset, so it needs to be discussed separately.
I don't think applying the same verbiage to time and timestamp will
produce anything except more confusion.

			regards, tom lane

---

From: Robert Treat <[email-masked]>
Date: Thu, 14 May 2026 14:36:05 -0400

> Adjusted seems like the wrong word. I think the correct word is converted.

I guess this depends on how you fundamentally think about timestamps
and/or time zones. When you select now(), whether it displays in -04,
+04, or UTC, the time itself is the same time; the time is not
adjusted nor converted, any more than xml or html data changes when
you apply different xslt or css towards it, we just store in one
particular manifestation of itself. The question is, do we store it in
a way where the storage format is cognizant (aka "aware") of the
concept of time zones, like timestamptz, in which case, you can modify
it's display and do timezone related maths in ways that the timezone
without time zone storage format cannot do, because it has no idea of
what a time zone is (ie. it is not aware of time zones as a concept).

Robert Treat
https://xzilla.net

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 14 May 2026 15:46:14 -0400

> produce anything except more confusion.

I see what you mean --- updated patch attached.

If we want to be more explicit we could change "including time zone
conversion" to "including time zone conversion to/from UTC".

index d8d91678e86..339fec8ac08 100644
@@ -264,13 +264,13 @@
 
       <row>
 
       <row>
 
      <para>