public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedPlease clarify that Timestamp with Timezone doesn't preserve the timezone.
15+ messages / 7 participants
[nested] [flat]
* Please clarify that Timestamp with Timezone doesn't preserve the timezone.
@ 2026-04-22 11:40 PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: PG Doc comments form @ 2026-04-22 11:40 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: github@richardneill.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
@ 2026-05-13 00:19 ` Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2026-05-13 00:19 UTC (permalink / raw)
To: github@richardneill.org; pgsql-docs@lists.postgresql.org
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?
--
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-13 02:21 ` Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Richard Neill @ 2026-05-13 02:21 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: github@richardneill.org; pgsql-docs@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
@ 2026-05-13 13:13 ` Bruce Momjian <bruce@momjian.us>
2026-05-13 14:59 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
0 siblings, 2 replies; 15+ messages in thread
From: Bruce Momjian @ 2026-05-13 13:13 UTC (permalink / raw)
To: Richard Neill <rjn@richardneill.org>; +Cc: github@richardneill.org; pgsql-docs@lists.postgresql.org
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.
Attachments:
[text/x-diff] time_zone.diff (809B, 2-time_zone.diff)
download | inline diff:
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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-13 14:59 ` Richard Neill <rjn@richardneill.org>
2026-05-13 16:48 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 16:35 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 2 replies; 15+ messages in thread
From: Richard Neill @ 2026-05-13 14:59 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: github@richardneill.org; pgsql-docs@lists.postgresql.org
Thanks for your reply,
On 13/05/2026 14:13, Bruce Momjian wrote:
> On Wed, May 13, 2026 at 03:21:11AM +0100, Richard Neill wrote:
>> Hi Bruce,
>>
>> 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.
Thanks.
>> 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
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.
>
>> 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?
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 14:59 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
@ 2026-05-13 16:48 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2026-05-13 16:48 UTC (permalink / raw)
To: Richard Neill <rjn@richardneill.org>; +Cc: github@richardneill.org; pgsql-docs@lists.postgresql.org
On Wed, May 13, 2026 at 03:59:11PM +0100, Richard Neill wrote:
> > 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
>
> 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)
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.
> 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)
Yes, I understand, but I have never heard anyone ask for this.
> > > 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?
>
> 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.
Uh, that would be a lot of detail for our docs. Maybe a blog post would
work for this.
--
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 14:59 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
@ 2026-05-14 16:35 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: David G. Johnston @ 2026-05-14 16:35 UTC (permalink / raw)
To: Richard Neill <rjn@richardneill.org>; +Cc: Bruce Momjian <bruce@momjian.us>; github@richardneill.org <github@richardneill.org>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Wednesday, May 13, 2026, Richard Neill <rjn@richardneill.org> wrote:
>
>
> 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)
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.
> 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)
Doesn’t exist, don’t see it being added.
>
> 2. timestamptz (a UTC value, where the database does the conversion
> implicitly for your configured local timezone, or explicitly if you ask)
Done.
>
> 3. timestamp_and_timezone (a struct, which contains timestamp, AND the
> timezone (or longitude) of the human who recorded it)
Not in core.
> 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.
This is what already exists in timestamp.
>
> 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.
A valid perspective but not one shared by the project. The absence of 3 is
not a bug.
>
>> 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?
>>
>
> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-13 15:57 ` Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 15+ messages in thread
From: Kirk Parker @ 2026-05-13 15:57 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Wed, May 13, 2026 at 6:13 AM Bruce Momjian <bruce@momjian.us> wrote:
> 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.
>
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.
> 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.
>
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.
> > 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?
>
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.
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
@ 2026-05-13 17:45 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2026-05-13 17:45 UTC (permalink / raw)
To: Kirk Parker <khp@equatoria.us>; +Cc: Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> On Wed, May 13, 2026 at 6:13 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> 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.
>
> 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.
> > 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.
>
> 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.
True, I have never seen this requested either.
> 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.
Agreed.
> 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.
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
--
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.
Attachments:
[text/x-diff] time_zone.diff (3.3K, 2-time_zone.diff)
download | inline diff:
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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-14 11:57 ` Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Robert Treat @ 2026-05-14 11:57 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Wed, May 13, 2026 at 1:45 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> > On Wed, May 13, 2026 at 6:13 AM Bruce Momjian <bruce@momjian.us> wrote:
> >
> > 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.
> >
> > 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.
>
> > > 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.
> >
> > 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.
>
> 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.
> > 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.
>
> Agreed.
>
> > 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.
>
> 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:
>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
@ 2026-05-14 12:05 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 16:39 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 18:36 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
0 siblings, 2 replies; 15+ messages in thread
From: Bruce Momjian @ 2026-05-14 12:05 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Thu, May 14, 2026 at 07:57:21AM -0400, Robert Treat wrote:
> > 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:
>
> 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?
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.
--
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-14 16:39 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 18:02 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 15+ messages in thread
From: Bruce Momjian @ 2026-05-14 16:39 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: Robert Treat <rob@xzilla.net>; Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Thu, May 14, 2026 at 08:17:36AM -0400, Dave Cramer wrote:
>
>
> On Thu, 14 May 2026 at 08:05, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Thu, May 14, 2026 at 07:57:21AM -0400, Robert Treat wrote:
> > > 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:
> >
> > 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?
>
> 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.
>
>
> Adjusted seems like the wrong word. I think the correct word is converted.
Okay, here is the patch with "conversion".
--
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.
Attachments:
[text/x-diff] time_zone.diff (3.3K, 2-time_zone.diff)
download | inline diff:
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>
@@ -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 conversion)</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 conversion</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 conversion</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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 16:39 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-14 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-05-14 19:46 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2026-05-14 18:02 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Dave Cramer <davecramer@postgres.rocks>; Robert Treat <rob@xzilla.net>; Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> writes:
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 16:39 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 18:02 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-05-14 19:46 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 15+ messages in thread
From: Bruce Momjian @ 2026-05-14 19:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dave Cramer <davecramer@postgres.rocks>; Robert Treat <rob@xzilla.net>; Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Thu, May 14, 2026 at 02:02:15PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
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".
--
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.
Attachments:
[text/x-diff] time_zone.diff (2.3K, 2-time_zone.diff)
download | inline diff:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index d8d91678e86..339fec8ac08 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -264,13 +264,13 @@
<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>
@@ -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 conversion)</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 conversion</entry>
<entry>4713 BC</entry>
<entry>294276 AD</entry>
<entry>1 microsecond</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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-13 15:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. Bruce Momjian <bruce@momjian.us>
@ 2026-05-14 18:36 ` Robert Treat <rob@xzilla.net>
1 sibling, 0 replies; 15+ messages in thread
From: Robert Treat @ 2026-05-14 18:36 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: Bruce Momjian <bruce@momjian.us>; Kirk Parker <khp@equatoria.us>; Richard Neill <rjn@richardneill.org>; github@richardneill.org; pgsql-docs@lists.postgresql.org
On Thu, May 14, 2026 at 8:17 AM Dave Cramer <davecramer@postgres.rocks> wrote:
> On Thu, 14 May 2026 at 08:05, Bruce Momjian <bruce@momjian.us> wrote:
>> On Thu, May 14, 2026 at 07:57:21AM -0400, Robert Treat wrote:
>> > > 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:
>> >
>> > 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?
>>
>> 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.
>>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-05-14 19:46 UTC | newest]
Thread overview: 15+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-22 11:40 Please clarify that Timestamp with Timezone doesn't preserve the timezone. PG Doc comments form <noreply@postgresql.org>
2026-05-13 00:19 ` Bruce Momjian <bruce@momjian.us>
2026-05-13 02:21 ` Richard Neill <rjn@richardneill.org>
2026-05-13 13:13 ` Bruce Momjian <bruce@momjian.us>
2026-05-13 14:59 ` Richard Neill <rjn@richardneill.org>
2026-05-13 16:48 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 16:35 ` David G. Johnston <david.g.johnston@gmail.com>
2026-05-13 15:57 ` Kirk Parker <khp@equatoria.us>
2026-05-13 17:45 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 11:57 ` Robert Treat <rob@xzilla.net>
2026-05-14 12:05 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 16:39 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-05-14 19:46 ` Bruce Momjian <bruce@momjian.us>
2026-05-14 18:36 ` Robert Treat <rob@xzilla.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox