Simon Willison’s Weblog

9th August 2024 - Link Blog

High-precision date/time in SQLite (via) Another neat SQLite extension from Anton Zhiyanov. sqlean-time (C source code here) implements high-precision time and date functions for SQLite, modeled after the design used by Go.

A time is stored as a 64 bit signed integer seconds 0001-01-01 00:00:00 UTC - signed so you can represent dates in the past using a negative number - plus a 32 bit integer of nanoseconds - combined into a a 13 byte internal representation that can be stored in a BLOB column.

A duration uses a 64-bit number of nanoseconds, representing values up to roughly 290 years.

Anton includes dozens of functions for parsing, displaying, truncating, extracting fields and converting to and from Unix timestamps.