Show HN: High-precision date/time in SQLite

Does this handle the special case of timezone changes (and local time discontinuity) that Jon Skeet famously documented?<p><a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;6841333&#x2F;why-is-subtracting-these-two-epoch-milli-times-in-year-1927-giving-a-strange-r" rel="nofollow">https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;6841333&#x2F;why-is-subtracti...</a><p>And computerphile explains so well in their 10-min video:<p><a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=-5wpm-gesOY" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=-5wpm-gesOY</a><p>---<p>I&#x27;ve long ago learned to never build my own Date&#x2F;Time nor Encryption libraries. There&#x27;s endless edge cases that can bite you hard.<p>(Which is also why I&#x27;m skeptical when I encounter new such libraries)

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

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<p><pre><code> SELECT * FROM my_table WHERE duration_s &gt;= 2h </code></pre> and have the database DWIM, converting &quot;2h&quot; to 7200.0 seconds and comparing like-for-like during the table scan.<p>Years ago, I wrote a special-purpose SQL database that had this kind of native unit handling, but I&#x27;ve seen nothing before or since, and it seems like a gap in the UI ecosystem.<p>And it shouldn&#x27;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.<p><pre><code> SELECT 2h + 15kg -- type error! </code></pre> Doing so would go a long way towards catching analysis errors early.

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

Very cool - definitely an important missing feature in SQlite.

I so wish that SQLite3 had an extensible type system.

This is a sort of lazy Ask HN: but in your experience, what is more useful &#x2F; valuable - nanosecond representation, or years outside the nano range of something like 1678-2200<p>I don&#x27;t do &quot;proper&quot; 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).<p>But being able to represent historical dates seems more likely to come up?<p>Thoughts?

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

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