[quotemstr]

Related tangent: databases should track units. If I have a time column, I should be able to say a column represents, say, durations in float64 seconds. Then I should be able to write

    SELECT * FROM my_table WHERE duration_s >= 2h

and have the database DWIM, converting "2h" to 7200.0 seconds and comparing like-for-like during the table scan.

Years ago, I wrote a special-purpose SQL database that had this kind of native unit handling, but I've seen nothing before or since, and it seems like a gap in the UI ecosystem.

And it shouldn't be for time. We should have the whole inventory of units --- mass, volume, information, temperature, and so on. Why not? We can also teach the database to reject mathematical nonsense, e.g.

    SELECT 2h + 15kg -- type error!

Doing so would go a long way towards catching analysis errors early.

[zokier]

Postgresql interval units allow already querying with natural-like expressions: https://www.postgresql.org/docs/current/datatype-datetime.ht...

[n_plus_1_acc]

What about leap seconds?

[quotemstr]

The leap second mechanism amounts to a collective agreement to rewrite chronological history. It's like a git rebase for your clock. Everyone (almost) in practice does math as if leap seconds never happened, and the consequent divergence from physical time ends up not mattering.

[SonOfLilit]

... no?

If we add a leap second at the end of 2025, nothing in 2024 gets rewritten. Only the future meaning of pointer expressions like "12 pm on January 2nd 2025" change their value. When I want exactly 48 hours after 12 pm Dec 31, I use a leap second independent time representation. But since usually I want the same thing everyone calls 12 pm Jan 2, I usually use a representation that gives me that.

And I, among many, take meticulous care to do my date math (for a bank core system) only in ways that naturally support leap seconds.