There's nothing in the linked blog post justifying why you might want a higher precision time. In fact the blog post would benefit from an explanation that outlines the current datetimes in sqlite and why they're insufficient.
Internally, SQLite uses milliseconds since the Julian day epoch stored as an 64-bit integer for date math, and uses the proleptic gregorian calendar (no leap seconds).
Externally, it uses either (a subset of) ISO 8601 with millisecond precision for a textual representation, or a IEEE 64-bit float representing either days since the Julian day epoch, or seconds since the Unix epoch.
You can also use integer seconds since the Unix epoch, but you'll get outside the supported date range before you use 53-bits, so 64-bit floats are equally safe.
The supported date range, btw., is from JDN zero through 9999-12-31T23:59:59.999.
In the 21st century, both of these representations can store the correctly rounded millisecond.
SQLite accepts time zone offsets, but immediately converts all times to UTC (or local time) and does not store the offset, so date functions can only output UTC or local time (no other time zone or offset).
