SQLite Forum

Convert datetime string to second since Epoch with millisecond precision
The "machine precision" of double precision floating point Unix epoch timestamps across the range of datetime that can round-trip SQLite3 should in theory be as follows:

sqlite> select ulp(unixtime('0000-01-01 00:00:00')), ulp(unixtime('9999-12-31 23:59:59.999'));
│ ulp(unixtime('0000-01-01 00:00:00')) │ ulp(unixtime('9999-12-31 23:59:59.999')) │
│ 7.62939453125e-06                    │ 3.0517578125e-05                         │

I've added a builtin UnixTime internal as follows:

static void _UnixTime(sqlite3_context *context, int argc, sqlite3_value **argv) {
    DateTime x;
    if (isDate(context, argc, argv, &x) == 0) {
        sqlite3_result_double(context, ((double)(x.iJD - 210866760000000ll)) / 1000.0);

This is basically the same as the builtin JulianDay function (in date.c) just calculating the return value differently.  Since the iJD is in milliseconds, the result should also be precise to the millisecond across the entire round-trip range.