[burntsushi]

Yeah the PostgreSQL situation is just utterly appalling. The fact that there is a type called "timestamp with time zone," that specifically calls out the fact that it has a time zone, but actually doesn't have a time zone is absolutely crazytown.

> This may seem redundant, but it's really important to answer "what happens if I have a future timestamp stored, and the USA suddenly rejects daylight savings time. Do I honor the absolute point in time, or do I honor the notion of 5pm?"

Yeah! It's great that Temporal rejects by default, but does let you override that and choose whether to respect the instant or respect to the civil time. And it lets you do that with a high level configuration knob. You don't have to code up the logic yourself.

[gmac]

I agree, but I believe Postgres is just following the SQL standard here?

What's even crazier is that writing plain TIMESTAMP gets you TIMESTAMP WITHOUT TIME ZONE, as is also mandated by the standard (the Postgres docs call this one out specifically). And that behaviour can be summarized as: not only don't store the timezone, but also ignore the timezone you get given.

For example, I'm on GMT/UTC right now, and I see this:

    select '2025-01-30T12:00:00-0800'::timestamp with time zone; -- 2025-01-30 20:00:00+00
    select '2025-01-30T12:00:00-0800'::timestamp; -- 2025-01-30 12:00:00

[burntsushi]

There are many valid and justifiable reasons to do crazy things. But it's still crazy. :-)

I don't think that completely absolves PostgreSQL though. It seems like they could add things to improve the situation and de-emphasize the use of TIMESTAMP and TIMESTAMP WITH TIME ZONE. But I am not a database or PostgreSQL expert, and there are assuredly trade-offs with doing this.

But yes, absolutely, the fact that TIMESTAMP is not just a timestamp without a time zone, but is actually a civil time is also equal parts crazytown. Like, a timestamp is 100% an instant in time. It is physical time. A number of seconds since an epoch. But PostreSQL (or the SQL standard) interprets it as a civil time? It's ludicrous and has assuredly confused countless humans. Especially those among us who don't know enough to question that PostgreSQL (or the SQL standard) might have gotten it wrong in the first place.

[akoboldfrying]

>the fact that it has a time zone, but actually doesn't have a time zone is absolutely crazytown.

It's always seemed reasonable to me. Sure, "TIMESTAMP WITH UTC OFFSET" would be even clearer -- but, as has been pointed out already, there are 2 valid ways you might want to handle time addition/subtraction, and only one of those ways enables addition to be done without pure speculation on what decisions will be made by political entities in the future, and PostgreSQL does it that way.

[burntsushi]

But I'm not critizing the behavior. I'm critizing the name.

And it's not even a timestamp with a UTC offset! It's just a Unix timestamp.

If you think the current naming is "reasonable," then we are likely living in two different planes of existence. And no amount of back-and-forth over the Internet is going to help. This is a level of dissonance that requires an in-person discussion in the pub to resolve.

[pas]

DB store data, app interpret data, user cry, developer eyes already dry.

(there's also https://github.com/mweber26/timestampandtz which is delightfully simple, 33 commits, last one 7 years ago)