On 2/6/19, Ben Asher <benashe...@gmail.com> wrote: > Hi there! We're having a debate at my company about date storage in SQLite. > SQLite has builtin support for ISO8601 in its date functions, so some folks > have started storing dates as ISO8601 SQLite-compatible date strings. Are > there pitfalls to storing dates this way compared to a unix timestamp? I'm > curious to know if anyone has experience and would highly recommend > sticking to one or the other for a particular reason. I'd also be grateful > if anyone could point me to any articles exploring this subject.
Integer unix timestamps are only accurate to one second, where ISO8601 (at least as implemented by SQLite) can go to 1 millisecond. Also you have to know the epoch to interpret a unix timestamp - not everybody uses 1970-01-01 00:00:00. Will people be able to figure out what the field value means when somebody discovers your data in 100 years? The SQLite implementation is *not* subject to the 2038-01-19 integer overflow problem. But other systems that might interact with SQLite are and so that is something to keep in mind as well. On the other hand, integer timestamps take up less space in the file. The third option is a fractional julian day number stored as an 8-byte floating point value. Such values are accurate to about 1 millisecond during the modern era, and it is much easier to compute the number of days by which two dates differ (you just subtract). There are no overflow crises pending, though precision does decay as you move further and further away from the epoch, though for dates in nearby centuries this is not a factor. Space requirements are in between integer unix timestamps and ISO8601 strings. In my own work, I have variously used ISO8601 text dates, unix timestamp integers, and fractional Julian Day numbers to represent dates and times, according to whichever worked best in that particular application. Since it is easy to convert between them all, this has never been a big problem. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
