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

21–30 of 77 posts

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

#21

Post by g15jv2dp »

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]

WTF. I'm interested in what you've created and want to understand the reason for your design decisions, and this is how you reply?

edit: Well, it seems that the parent comment has been edited. But honestly, after reading the initial comment, I'm not interested in engaging in any way whatsoever with this person.

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

#22

Post by marcellus23 »

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]

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

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

#23

Post by nalgeon »

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?

Storing unix timestamp as nanoseconds is not Go's style, but you can do just that with this extension.

    select time_to_nano(time_now());
    -- 1722979335431295000

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

#24

Post by nalgeon »

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…

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

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

#25

Post by [deleted] »

marcellus23 wrote: post #22
nalgeon wrote: post #20

Earlier quoted context omitted.

[flagged]

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]

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

#26

Post by lifeisstillgood »

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 come up?

Thoughts?

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

#27

Post by [deleted] »

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.

[deleted]

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

#28

Post by zokier »

ralferoo wrote: post #12
zokier wrote: post #6

I just wish people would stop using the phrase "seconds since epoch" (or equivalent) unless that is exactly what they mean. I wonder what does select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); return?

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.

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

#30

Post by 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.
Post reply on HN