Thread

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

   The Post Office <noreply@postgresql.org> — 2026-04-22T11:40:49Z

   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
   
   
   
   
   

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

   Bruce Momjian <bruce@momjian.us> — 2026-05-13T00:19:29Z

   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.
   
   
   
   

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

   Richard Neill <rjn@richardneill.org> — 2026-05-13T02:21:11Z

   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?
   
   
   
   

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

   Bruce Momjian <bruce@momjian.us> — 2026-05-13T13:13:10Z

   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.
   

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

   Richard Neill <rjn@richardneill.org> — 2026-05-13T14:59:11Z

   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
   
   
   
   
   
   
   
   
   
   
   

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

   Kirk Parker <khp@equatoria.us> — 2026-05-13T15:57:54Z

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

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

   Bruce Momjian <bruce@momjian.us> — 2026-05-13T16:48:03Z

   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.
   
   
   
   

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

   Bruce Momjian <bruce@momjian.us> — 2026-05-13T17:45:15Z

   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.
   

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

   Robert Treat <rob@xzilla.net> — 2026-05-14T11:57:21Z

   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
   
   
   
   

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

    Bruce Momjian <bruce@momjian.us> — 2026-05-14T12:05:23Z

    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.
    
    
    
    

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

    David G. Johnston <david.g.johnston@gmail.com> — 2026-05-14T16:35:19Z

    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.
    

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

    Bruce Momjian <bruce@momjian.us> — 2026-05-14T16:39:48Z

    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.
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-05-14T18:02:15Z

    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
    
    
    
    

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

    Robert Treat <rob@xzilla.net> — 2026-05-14T18:36:05Z

    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
    
    
    
    

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

    Bruce Momjian <bruce@momjian.us> — 2026-05-14T19:46:14Z

    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.