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
