Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: High-precision date/time in SQLite

antonz.org

Post reply on HN
Print view View original

41–50 of 77 posts

Re: Show HN: High-precision date/time in SQLite

#41

Post by fluoridation »

edflsafoiewq wrote: post #37
ralferoo wrote: post #36

Earlier quoted context omitted.

> it doesn't mean literally seconds since epoch, but instead some unix-style monstrosity That "unix-style monstrosity" is literally seconds since the UNIX time epoch, which is unambiguously defined as starting on 1970-1-1 0:00:00 UTC. Or it would have been, had leap seconds not been forced upon the world in 1972, at which point yes, arguably it's no longer "physical earth seconds" since the epoch but "UNIX seconds" w…

You literally did the exact thing GP is complaining about.

It's unclear if that's what they're complaining about, but if it is, the reason people just say "seconds since epoch" instead of saying what they actually mean is precisely because no one wants to get into the weeds of leap seconds. POSIX timestamps are a convenient format and they represent an idealized "second" of constant length that's in a uniform correspondence with the rotation of the Earth. Most likely if you're using such a representation you won't care that 1723740711 is not literally and exactly 1723740711 seconds since 1970-01-01T00:00:00Z and the error of a few seconds will not even be noticed.

Re: Show HN: High-precision date/time in SQLite

#42

Post by ralferoo »

edflsafoiewq wrote: post #37
ralferoo wrote: post #36

Earlier quoted context omitted.

> it doesn't mean literally seconds since epoch, but instead some unix-style monstrosity That "unix-style monstrosity" is literally seconds since the UNIX time epoch, which is unambiguously defined as starting on 1970-1-1 0:00:00 UTC. Or it would have been, had leap seconds not been forced upon the world in 1972, at which point yes, arguably it's no longer "physical earth seconds" since the epoch but "UNIX seconds" w…

You literally did the exact thing GP is complaining about.

Again, the same question: what would be a better solution?

Re: Show HN: High-precision date/time in SQLite

#43

Post by out_of_protocol »

commodoreboxer wrote: post #18
out_of_protocol wrote: post #16

Why not go golang style, unix timestamp as nanoseconds, in signed int64. Maybe you can't cover millions of years with nanosecond precision, do you really need it?

With that precision and size, you can only cover the years from 1678 to 2262, which strongly limits your ability to represent historical dates and times.

> represent historical dates and times.

With nanosecond precision? Just decide what you want to do beforehand, i bet even datetime don't make much sense for that time period, bare date would suffice. also, you'll likely need location, calendar system etc since real dates were not that standardized back then

Re: Show HN: High-precision date/time in SQLite

#44

Post by alberth »

nalgeon wrote: post #24
sltkr wrote: post #17

Earlier quoted context omitted.

This library doesn't deal with the notion of local time at all. It's all UTC-based times, possibly with a user-supplied timezone offset, but then the hard part of calculating the timezone offset must be done by the caller. I do think the documentation could be a little clearer. The author talks about “time zones” but the library only deals with time zone offsets. (A time zone is something like America/New_York, while…

Thanks for the suggestion! True, only fixed offsets are supported, not timezone names.

@nalgeon

Do you plan to address the use cases in the SO post, or asked differently - what is the intended use case of this library?

I tried to recreate it on your site (which is very cool btw in allowing the code to run in browser) and it seems to fail and give the wrong time difference.

  select time_compare(time_date(1927, 12, 31, 23, 58, 08, 0, 28800000), time_date(1927, 12, 31, 23, 58, 09, 0, 28800000));
Results in an answer of '1', which is incorrect.

Please don't take my comments as being negative or unappreciated, this is super difficult stuff and anyone who tries to make the world an easier place should be thanked for that. So thank you.

----

EDIT: this post explains why the answer isn't "1"

https://stackoverflow.com/questions/6841333/why-is-subtracti...

Re: Show HN: High-precision date/time in SQLite

#45

Post by n_plus_1_acc »

quotemstr wrote: post #30

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…

What about leap seconds?

Re: Show HN: High-precision date/time in SQLite

#46

Post by nalgeon »

alberth wrote: post #44
nalgeon wrote: post #24

Earlier quoted context omitted.

Thanks for the suggestion! True, only fixed offsets are supported, not timezone names.

@nalgeon Do you plan to address the use cases in the SO post, or asked differently - what is the intended use case of this library? I tried to recreate it on your site (which is very cool btw in allowing the code to run in browser) and it seems to fail and give the wrong time difference. select time_compare(time_date(1927, 12, 31, 23, 58, 08, 0, 28800000), time_date(1927, 12, 31, 23, 58, 09, 0, 28800000)); Results in…

I appreciate your comments, and thank you for trying out the extension.

This query returns -1 (minus one, not one), which seems correct to me. The first date is before the second:

    select time_compare(
      time_date(1927, 12, 31, 23, 58, 08, 0, 28800000),
      time_date(1927, 12, 31, 23, 58, 09, 0, 28800000)
    );

    -1

Re: Show HN: High-precision date/time in SQLite

#47

Post by quotemstr »

n_plus_1_acc wrote: post #45
quotemstr wrote: post #30

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…

What about leap seconds?

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.

Re: Show HN: High-precision date/time in SQLite

#48

Post by edflsafoiewq »

fluoridation wrote: post #41
edflsafoiewq wrote: post #37

Earlier quoted context omitted.

You literally did the exact thing GP is complaining about.

It's unclear if that's what they're complaining about, but if it is, the reason people just say "seconds since epoch" instead of saying what they actually mean is precisely because no one wants to get into the weeds of leap seconds. POSIX timestamps are a convenient format and they represent an idealized "second" of constant length that's in a uniform correspondence with the rotation of the Earth. Most likely if you'…

No, that is literally exactly what they just complained about.

> And it's annoying that you need to read some footnote to figure out what exactly it means; it's annoying that it is basically a code-phrase that you just need to know that it's not supposed to be taken literally.

Even to point of deferring the real explanation to a secondary paragraph.

Re: Show HN: High-precision date/time in SQLite

#49

Post by Someone »

sltkr wrote: post #17
alberth wrote: post #14

Does this handle the special case of timezone changes (and local time discontinuity) that Jon Skeet famously documented? https://stackoverflow.com/questions/6841333/why-is-subtracti... And computerphile explains so well in their 10-min video: https://www.youtube.com/watch?v=-5wpm-gesOY --- I've long ago learned to never build my own Date/Time nor Encryption libraries. There's endless edge cases that can bite you hard…

This library doesn't deal with the notion of local time at all. It's all UTC-based times, possibly with a user-supplied timezone offset, but then the hard part of calculating the timezone offset must be done by the caller. I do think the documentation could be a little clearer. The author talks about “time zones” but the library only deals with time zone offsets. (A time zone is something like America/New_York, while…

> It's all UTC-based times

Not even that. UTC has leap seconds, which this code doesn’t handle (FTA: “The calendrical calculations always assume a Gregorian calendar, with no leap seconds”)

It copies that from the golang time package, which makes the same claim (https://pkg.go.dev/time)

That makes life a lot simpler for the implementer, but doesn’t that mean you can only reliably use these two libraries for computing with durations, not with moments in time or vice versa? The moment you start mapping these times to real world clocks and adding durations to them, you run the risk of getting small (up to about half a minute, at the moment) inconsistencies.

Re: Show HN: High-precision date/time in SQLite

#50

Post by michaelt »

mynameisash wrote: post #9

I find the three different time representations/sizes curious (eg, what possible use case would need nanosecond precision over a span of billions of years?). More confusing is that there's pretty extreme time granularity, but only ±290 years range with nanosecond precision for time durations?

> what possible use case would need nanosecond precision over a span of billions of years?

Once you've decided you're using nanosecond precision, a 64-bit representation can only cover 584 years which ain't enough. You really want at least 2 more bits, so you can represent 2024 years.

But once you're adding on 2 bits, why not just add on 16 or even 32? Then your library can cover the needs of everyone from people calculating how it takes light to travel 30cm, to people calculating the age of the universe.

That's how I imagine the design decisions went, anyway :)

Of course you can't really provide sub-second accuracy without leapsecond support and what does pre-human-civilisation leapsecond support even mean?

Post reply on HN