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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
