10 ms·
Show HN: High-precision date/time in SQLite
- davidhyde 2y agoI think it’s important to be explicit about whether or not signed integers are used. From reading the document it seems that they may be signed but they could not be. If they are signed then you could have multiple bit strings that represent the same date and time which is not great.
- deleted 2y ago[deleted]
- jagged-chisel 2y agoDefinitely signed - “use negative duration to subtract” But bit pattern is an issue internal to the library. If you can find a bug in the code, certainly point it out and offer a fix if it’s in your skillset.
- sigseg1v 2y agoI think the negative number here refers to the amount of days/etc to subtract (eg. add negative days to subtract, not supply a negative date). However, at the same time it seems to indicate that it stores data using sqlites built in number type, which to my understanding does not support unsigned? Secondly, the docs mention you can store with a range of 290 years and the precision is nanoseconds, which if you calculate it out works out to about 63 bits of information, suggesting a signed implementation.
- tyingq 2y agoYes, it's signed...https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes: # some omitted... INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
- gcr 2y agoSubtraction of unsigned negative values still works just fine because of two’s compliment. (uint8)(-3) is 253, for example, and (uint8)5-(uint8)253 = (uint8)8, corresponding to 5 - (-3)
- kaoD 2y ago> multiple bit strings that represent the same date and time How so?
- davidhyde 2y agoYou’re right, whether or not the integers are signed has nothing to do with the issue above. Unsigned integers have the same issue. Here is an example for signed integers. These represent zero time but have different representations in memory: Seconds: 2 Nanoseconds: -2,000,000,000 (fits in a 32 bit number) Time: zero seconds Seconds: -2 Nanoseconds: 2,000,000,000 Time: zero seconds Here is an example for unsigned: Seconds: 1 Nanoseconds: 0 Time: 1 second Seconds: 0 Nanoseconds: 1,000,000,000 Time 1 second
- kaoD 2y agoThanks, but I'm not gonna pretend that was my point. Dumb question from me, I just forgot the context that time was a pair of integers and was utterly confused, haha. You're spot on!
- zokier 2y agoI 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?
- ralferoo 2y agoWhy 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 used by most computer systems, so all bets are off. Perhaps it'll return MAX_INT, MIN_INT, 0, something that's plausible but doesn't take into calendar reforms that have no bearing on the epoch being used, or perhaps it translates into a different epoch and calculates the exact number of seconds, or anything else. One could even argue that there are no valid epochs before GMT/UTC because it was all just local time before then. But of course, you can argue either way whether -ve values should be supported. Exactly 24 hours before 1970-1-1 0:00:00 UTC could be reasonably expected to be -86400, on the other hand "since" strongly implies positive only. Other people might have entirely different epochs for different reasons, again within the domain it's being used, that's fine as long as everyone agrees. Or did you have some other objection?
- zokier 2y agoThe 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.
- ralferoo 2y ago> 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?
- simontheowl 2y agoVery cool - definitely an important missing feature in SQlite.
- mynameisash 2y agoI 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?
- nalgeon 2y agoIt works very well for me and thousands of other Go developers. That's why I chose this approach.
- g15jv2dp 2y agoThere'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.
- nalgeon 2y ago[flagged]
- g15jv2dp 2y agoWTF. 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.
- marcellus23 2y agoI 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
- alberth 2y agoDoes this handle the special case of timezone changes (and local time discontinuity) that Jon Skeet famously documented? https://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-epoch-milli-times-in-year-1927-giving-a-strange-r 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 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. (Which is also why I'm skeptical when I encounter new such libraries)
- sltkr 2y agoThis 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 a time zone offset is the difference to UTC time, which is -14400 seconds for New York today, but will be -18000 in a few months due to daylight saving time changes.)
- nalgeon 2y agoThanks for the suggestion! True, only fixed offsets are supported, not timezone names.
- alberth 2y ago@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-subtracting-these-two-epoch-milli-times-in-year-1927-giving-a-strange-r https://stackoverflow.com/questions/6841333/why-is-subtracti...
- out_of_protocol 2y agoWhy 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?
- commodoreboxer 2y agoWith 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 2y ago[deleted]
- azornathogron 2y agoIf 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.
- out_of_protocol 2y ago> 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
- nalgeon 2y agoStoring 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
- lifeisstillgood 2y agoThis 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?
- cyberax 2y agoHistorical dates, for sure. Simply reducing the precision to 10ns will provide enough range in practice.
- rokkamokka 2y agoA bit like asking if a hammer or a screwdriver is more useful. It depends on the work
- deleted 2y ago[deleted]
- cryptonector 2y agoI so wish that SQLite3 had an extensible type system.
- funny_falcon 2y agoAs a PostgreSQL smallish contributor I just can say: NO, DON'T DO THIS!!!! Extensible type system is a worst thing that could happend with database end-user performance. Then one may not short-cut no single thing in query parsing and optimization: you must check type of any single operand, find correct operator implemenation, find correct index operator family/class and many more all through querying system catalog. And input/output of values are also goes through the functions, stored in system catalog. You may not even answer to "select 1" without consulting with system catalog. There should be sane set of builtin types + struct/json like way of composition. That is like most DBs do except PostgreSQL. And I strongly believe it is right way.
- cryptonector 2y ago> you must check type of any single operand, find correct operator implemenation, find correct index operator family/class and many more all through querying system catalog. Not with static typing. The problem with PG is that it's not fully statically typed internally. SQLite3 is worse still, naturally. But a statically typed SQL RDBMS should be possible.
- funny_falcon 2y agoWhat “Statically typed” would mean for SQL DB with extensible type system?
- cryptonector 2y agoIt means you can define new types, not that you can store values of arbitrary types in columns you already have.
- quotemstr 2y agoRelated 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.
- Abismith 2y ago[flagged]