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

31–40 of 77 posts

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

#31

Post by cyberax »

lifeisstillgood wrote: post #26

This is a sort of lazy Ask HN: but in your experience, what is more useful / valuable - nanosecond representation, or years outside the nano range of something like 1678-2200 I don't do "proper" science so the value of nanoseconds seems limited to very clever experiments (or some financial trade tracking that is probalby even more limited in scope). But being able to represent historical dates seems more likely to co…

Historical dates, for sure.

Simply reducing the precision to 10ns will provide enough range in practice.

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

#32

Post by rokkamokka »

lifeisstillgood wrote: post #26

This is a sort of lazy Ask HN: but in your experience, what is more useful / valuable - nanosecond representation, or years outside the nano range of something like 1678-2200 I don't do "proper" science so the value of nanoseconds seems limited to very clever experiments (or some financial trade tracking that is probalby even more limited in scope). But being able to represent historical dates seems more likely to co…

A bit like asking if a hammer or a screwdriver is more useful. It depends on the work

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

#33

Post by zokier »

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…

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

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

#34

Post by [deleted] »

[deleted] wrote: post #25
marcellus23 wrote: post #22

Earlier quoted context omitted.

I think you're being overly defensive. The GP is curious about the decisions you made, and just asking questions in a bit of a blunt (but not rude or accusatory) style that's typical for HN. edit: the comment I'm responding to was much more vitriolic, it's since been edited

[deleted]

[deleted]

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

#35

Post by [deleted] »

lifeisstillgood wrote: post #26

This is a sort of lazy Ask HN: but in your experience, what is more useful / valuable - nanosecond representation, or years outside the nano range of something like 1678-2200 I don't do "proper" science so the value of nanoseconds seems limited to very clever experiments (or some financial trade tracking that is probalby even more limited in scope). But being able to represent historical dates seems more likely to co…

[deleted]

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

#36

Post by ralferoo »

zokier wrote: post #28
ralferoo wrote: post #12

Earlier quoted context omitted.

Why do you wish that? I can think of a few plausible reasons, but the only one that is really significant is "what epoch"? In the case of UNIX-based systems and systems that try to mimic that behaviour, that is well defined. But as you haven't said what your complaints are, it's hard to provide any counterpoint or justification for why things are as they are. > time_date(1311, 11, 18) That isn't defined in the epoch…

The problem with "seconds since epoch" expression is that almost always it doesn't mean literally seconds since epoch, but instead some unix-style monstrosity. 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.

> 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" where a day is defined as exactly 86400 UNIX seconds.

In retrospect, it'd have been better if UNIX time was exactly a second, and the leap seconds accounted for by the tz database, but that didn't exist until over a decade after the first leap seconds were added, so probably everybody thought it was easier just to take the pragmatic option to skip the missing seconds, exactly the same way that the rest of the world was doing.

I'm still not sure if that's what your complaint is about, as I don't know of time systems defined any other way handle this correctly if you were to ask for the time difference in seconds between a time before and after a leap second.

Maybe a better question would be: what do you think would be a better way of defining a representation of a date and time, and that would allow for easy calculations and also easy transformations into how it's presented for users?

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

#37

Post by edflsafoiewq »

ralferoo wrote: post #36
zokier wrote: post #28

Earlier quoted context omitted.

The problem with "seconds since epoch" expression is that almost always it doesn't mean literally seconds since epoch, but instead some unix-style monstrosity. 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.

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

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

#38

Post by bongodongobob »

nalgeon wrote: post #11
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?

It works very well for me and thousands of other Go developers. That's why I chose this approach.

Nice. Smoking cigarettes works for me and millions of others but it's still stupid and will take years or decades of your life.

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

#39

Post by azornathogron »

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.

If you're representing dates back into the 1600s you need to keep in mind that calendar maths and things like "was this year a leap year" become more complicated. The Gregorian calendar was introduced in the 1500s but worldwide adoption took a long time - for example, the UK didn't adopt it until the 1700s. So you've got more than a century where just having "a date" isn't really sufficient information to know when something happened, you'll need to also know what calendar system that date is in.

Overall, this means if you're representing historical dates I would question whether a seconds-since-epoch timestamp representation is what you want at all, regardless of range and precision.

Edit: yes, you can kinda handle this as part of handling timezones, but still, it's complicated enough that you may want to retain more or different information if you're displaying or letting users enter historical dates.

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

#40

Post by goldfishgold »

nalgeon wrote: post #20
g15jv2dp wrote: post #19

Earlier quoted context omitted.

There's no reason it wouldn't "work", the question is "why". Having such precise dates obviously comes with some compromises (e.g., the representation is larger, or it's variable depending on the value which comes with additional complexity, etc.). So surely there must be some pros to counterbalance the cons. "Because it's what Go does" is an answer, but I don't know if it's a convincing one.

[flagged]

There's nothing in the linked blog post justifying why you might want a higher precision time. In fact the blog post would benefit from an explanation that outlines the current datetimes in sqlite and why they're insufficient.
Post reply on HN