undefined | Better HN

0 pointsgmac1y ago0 comments

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

0 comments

burntsushi1y ago

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.

j / k navigate · click thread line to collapse