SQLite Forum

Convert datetime string to second since Epoch with millisecond precision
Hi again. My requirements have changed. I need to preserve micro-second precision.

And I'm getting surprising results, as shown below, where I *gain* 9us on a date-time  
with no fractional seconds, lose 1005us on that datetime when it has ms precision,  
and finally lose 455us when it has us precision.

Is there a way to retain full precision, while still storing a numeric date-time,  
when micro-seconds precision is required? I need to accurately *round-trip* those.

AFAIK, SQLite's real data-type is a full IEEE double, i.e. with at least 15 digits  
of precision, so I can understand maybe 10us fuzziness, but 1005us points to an issue, no?

I'm also OK storing the micro-seconds since Epoch date-time as a integer, to have  
the full 64-bit precision, instead of the 53-bit one from the real's significand.

But then how do I get that exact number of micro-seconds if `julianday` is not precise enough?

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(s text, f real);
sqlite> insert into t(s) values ('2016-06-13T09:36:34Z');
sqlite> insert into t(s) values ('2016-06-13T09:36:34.123Z');
sqlite> insert into t(s) values ('2016-06-13T09:36:34.123456Z');
sqlite> update t set f = (julianday(s) - 2440587.5) * 86400.0;
sqlite> .mode box
sqlite> select * from t;
│              s              │        f         │
│ 2016-06-13T09:36:34Z        │ 1465810594.00001 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.122   │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123   │
sqlite> select s, printf("%.6f", f) from t;
│              s              │ printf("%.6f", f) │
│ 2016-06-13T09:36:34Z        │ 1465810594.000009 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.121995 │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123001 │