3 ms·
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 s
by quotemstr 2y ago
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 2y agoPostgresql interval units allow already querying with natural-like expressions: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT https://www.postgresql.org/docs/current/datatype-datetime.ht...
- n_plus_1_acc 2y agoWhat about leap seconds?
- quotemstr 2y agoThe 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 2y ago... 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.