Show HN: High-precision date/time in SQLite
1–10 of 77 posts
Re: Show HN: High-precision date/time in SQLite
#2I 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.
Re: Show HN: High-precision date/time in SQLite
#3I 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]
Re: Show HN: High-precision date/time in SQLite
#4I 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.
Definitely 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.
Re: Show HN: High-precision date/time in SQLite
#5I 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.
Definitely 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.
I 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.
Re: Show HN: High-precision date/time in SQLite
#6I 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));
Re: Show HN: High-precision date/time in SQLite
#7Very cool - definitely an important missing feature in SQlite.
Re: Show HN: High-precision date/time in SQLite
#8I 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.
Definitely 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.
Subtraction 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)
Re: Show HN: High-precision date/time in SQLite
#9I 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?
Re: Show HN: High-precision date/time in SQLite
#10Earlier quoted context omitted.
Definitely 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.
I 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 calcula…
Yes, it's signed...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.