[phiresky]

That but also and for me more importantly it does not tell you how to add time - If you add one month of hours to a date in October, it depends on the timezone whether you will end up one hour of local time earlier or not (due to DST), because then +02:00 might be +01:00

[roywashere]

While this is true, most often if you want to do "now plus a month" you'll mean "at the same time on the local clock", and disregarding timezone changes, while most often if you want to do "now plus four hours" you'd actually mean four real hours, and you want to calculate in the DST changes to make sure you have four actual hours in your duration

[burntsushi]

That's how Temporal works:

    >> zdt = Temporal.ZonedDateTime.from("2024-03-09T17:00-05[US/Eastern]")
    >> zdt.add("P1d").toString()
    "2024-03-10T17:00:00-04:00[US/Eastern]"
    >> zdt.add("PT24h").toString()
    "2024-03-10T18:00:00-04:00[US/Eastern]"

If you don't have the time zone and instead just an offset, then Temporal can't do this:

    >> zdt = Temporal.ZonedDateTime.from("2024-03-09T17:00-05[-05]")
    >> zdt.add("P1d").toString()
    "2024-03-10T17:00:00-05:00[-05:00]"
    >> zdt.add("PT24h").toString()
    "2024-03-10T17:00:00-05:00[-05:00]"

Adding 1 day still works the same with respect to civil time but the offset is wrong since it doesn't account for the DST change. And adding 24 hours leads to a different result as well. This is one of the reasons why RFC 9557 is so important for serializing zoned datetimes when you want your arithmetic to be DST safe. Previously, to get this right, you had to include a time zone out of band somehow along with your RFC 3339 timestamp.

[btown]

https://datatracker.ietf.org/doc/rfc9557/ for folks' reference about the new [] suffix syntax.

It's a really well thought out RFC: the offset, the civil time zone name, and a flag for whether that civil time zone is critical information can all be stored, and an inconsistency marked critical MUST be acted upon by the application explicitly, either by rejecting or requesting user interaction.

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?"

Unfortunately, there's going to be a lot of chaos if this happens. Systems like Postgres only store the absolute point in time, normalized to UTC, despite what the name "timestamp with time zone" might imply; an application team or DBA making a decision about this might need to look at other domain-specific metadata e.g. the physical location of the associated asset to determine whether to add or remove an hour. I shudder to think about what this might imply for e.g. HIPAA protected medical systems; the impact of the ensuing bugs might be measured in lives.

[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.

[valenterry]

> most often if you want to do

most often it is "most often" that causes bugs in software. And when related to date/time it often are severe bugs. ;-)