On Wed, May 13, 2026 at 1:45 PM Bruce Momjian <[email protected]> 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 <[email protected]> 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
