SQLite Forum

Convert datetime string to second since Epoch with millisecond precision
Login

Convert datetime string to second since Epoch with millisecond precision

(1) By ddevienne on 2020-03-17 16:09:17 [link] [source]

Hi. Below does what I want, but surely there's a better way than parsing the datetime 3 times? Because the below is ugly as hell. Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select
   ...>        cast(strftime('%s', '2016-06-13T09:36:34.123Z') as real) +
   ...>             strftime('%f', '2016-06-13T09:36:34.123Z') -
   ...>        cast(strftime('%S', '2016-06-13T09:36:34.123Z') as real)
   ...> ;
1465810594.123
sqlite>

(2.1) By Keith Medcalf (kmedcalf) on 2020-03-26 19:34:10 edited from 2.0 in reply to 1 [link] [source]

Convert to julianday, which is floating point, correct the offset (origin date) and then correct the slope (unit scale) ...

select (strftime('%J', '2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0;
1465810594.123

or

select (julianday('2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0;
1465810594.123

The number 2440587.5 is the epoch adjustment to the unix epoch date:

sqlite> select julianday('1970-01-01 00:00:00.000Z');
2440587.5

(3) By ddevienne on 2020-03-17 17:23:08 in reply to 2.0 [link] [source]

Thanks Keith, as always!

This ought to be built-in format though IMHO. Too clever otherwise. My $0.02. --DD

(5) By Keith Medcalf (kmedcalf) on 2020-03-17 20:07:15 in reply to 3 [link] [source]

Note that the builtin julianday is probably far more efficient. Internally, a datetime is stored as a 64-bit offset in milliseconds from the julian epoch.

It takes all the same parameters as the rest of the datetime functions to initialize this internal offset -- and the return value is merely "converting" that iJD offset into a floating point number of days since the epoch.

strftime('%J', ...) is syntactic sugar for printf('%.16g', julianday(...))

Both take all the same arguments (...) however strftime returns a character string that is the result of converting the double julianday number to text, whereas julianday simply returns the double directly -- so if you are going to be doing further calculations there is no point in converting a double to text then back to a double again.

Since the internal datetime is stored in milliseconds since the julian epoch, the result calculated on the julianday() function should be precise to the millisecond right up until the year 10K problem is happening.

(7) By John McMahon (jmcm54ac) on 2020-03-26 07:21:25 in reply to 5 [link] [source]

Would it be possible to have the above examples added to the examples in the "lang_datefunc" document. I think this could be/become a FAQ. After correcting the offset value in the "julianday" example of coarse.

John

(9) By ddevienne on 2020-04-22 13:54:52 in reply to 2.1 [link] [source]

I've recently discovered that this doesn't work with timezone suffixes,
using format YYYY-MM-DDThh:mm:ssZ[+/-]hh:mm. I'm getting back nulls.

Can SQLite deal with the (optional) TZ offset suffixes? Thanks, --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .null NULL
sqlite> select (julianday('2014-09-09T15:33:25Z+01:00') - 2440587.5) * 86400.0,
   ...>        (julianday('2014-09-09T15:33:25Z+1') - 2440587.5) * 86400.0,
   ...>        (julianday('2014-09-09T15:33:25Z01:00') - 2440587.5) * 86400.0,
   ...>        (julianday('2014-09-09T15:33:25Z-01:00') - 2440587.5) * 86400.0;
NULL|NULL|NULL|NULL

(10) By Gunter Hick (gunter_hick) on 2020-04-22 14:08:55 in reply to 9 [source]

Works as advertised, see https://sqlite.org/lang_datefunc.html

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z".

(11) By ddevienne on 2020-04-22 14:45:34 in reply to 10 [link] [source]

Thanks. Yes, my mistake, it's either Z or [+-]HH:MM as you correctly pointed out. Silly me.

(12) By ddevienne on 2020-11-06 16:46:50 in reply to 2.1 [link] [source]

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?

C:\Users\ddevienne>sqlite3
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 │
└─────────────────────────────┴───────────────────┘

(13) By Keith Medcalf (kmedcalf) on 2020-11-06 21:25:28 in reply to 12 [link] [source]

You need to round half-even to 3 decimal places.

The internal datetime is only stored to millisecond precision (in milliseconds since the julian epoch), so you will never no matter what you do get more accuracy than a millisecond. You cannot put back what is not there in the first place. Just as you cannot reconstruct quality video from compressed all to ratshit video no matter what the screaming media companies (or others) tell you.

Secondly, you are running on Windows x64. From your results I would guess that you are also using a Microsoft compiler on x64. How do I know? Microsoft compilers cannot do floating point arithmetic. Either (a) switch to 32-bit which can do arithmetic properly or (b) switch to a different compiler.

How do I divine this magical information? Simple. I am running on Windows 10 x64 and and using the Mingw64 GCC compiler, and it gets "more better" answers to the same questions (floating point arithmetic is performed to within .5 ULP) -- it is a trade-off though because while MSVC cannot do floating point arithmetic properly, it gets correct answers for its mathematical functions (ln/log/exp/sin/cos etc) -- MinGW64 GCC the situation is reversed in that it can do arithmetic correctly but cannot compute a math function (ln/log/exp/sin/cos).

SQLite version 3.34.0 2020-11-05 19:31:02
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> select * from t;
┌─────────────────────────────┬──────────────────┐
│              s              │        f         │
├─────────────────────────────┼──────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.00001 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.123   │
│ 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.123001 │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123001 │
└─────────────────────────────┴───────────────────┘
sqlite> .version
SQLite 3.34.0 2020-11-05 19:31:02 91cd3839204a000f79887243fbbb1678f185b14e479b5b6781b057e7ec5cec7c
zlib version 1.2.11
gcc-9.1.0
sqlite>

Secondly, do not forget that any access to the clock (or wait states, sleeps, dispatcher preemption/scheduling, etc) has a granularity of just shy of a 16 ms "system tick" by default unless you have changed the frequency of the system timer.

(15) By ddevienne on 2020-11-07 00:47:14 in reply to 13 [link] [source]

From your results I would guess that you are also using a Microsoft compiler on x64

Actually, I picked up the official 32-bit package from the Download area...

i.e. https://www.sqlite.org/2020/sqlite-tools-win32-x86-3330000.zip

(17) By Keith Medcalf (kmedcalf) on 2020-11-07 02:16:03 in reply to 15 [link] [source]

Fascinating, because I get the "correct" results with MSVC x64 as well as my GCC 9.1.0 MinGW64, but the results of the downloaded executable (apparently compiled with GCC-5.2.0 of some description) the julianday result is out by 25 ULP which results in the computed unixtime being out by 4219 ULP ...

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> .version
SQLite 3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f
zlib version 1.2.11
gcc-5.2.0
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> 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('%!.16f', f) from t;
┌─────────────────────────────┬─────────────────────────────┐
│              s              │     printf('%!.16f', f)     │
├─────────────────────────────┼─────────────────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.0000085831015352 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.1219954490226107 │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.1230013371037732 │
└─────────────────────────────┴─────────────────────────────┘
sqlite>

SQLite version 3.34.0 2020-11-05 19:31:02
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.34.0 2020-11-05 19:31:02 91cd3839204a000f79887243fbbb1678f185b14e479b5b6781b057e7ec5cec7c
zlib version 1.2.11
gcc-9.1.0
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> select * from t;
┌─────────────────────────────┬──────────────────┐
│              s              │        f         │
├─────────────────────────────┼──────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.00001 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.123   │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123   │
└─────────────────────────────┴──────────────────┘
sqlite> select s, printf('%!.16f', f) from t;
┌─────────────────────────────┬─────────────────────────────┐
│              s              │     printf('%!.16f', f)     │
├─────────────────────────────┼─────────────────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.0000085830688477 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.1230013370513916 │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.1230013370513916 │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select ulps((julianday('2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0, 1465810594.1219954490226107);
┌──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ulps((julianday('2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0, 1465810594.1219954490226107) │
├──────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 4219.0                                                                                           │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘
sqlite>

SQLite version 3.34.0 2020-11-05 19:31:02
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.34.0 2020-11-05 19:31:02 91cd3839204a000f79887243fbbb1678f185b14e479b5b6781b057e7ec5cec7c
zlib version 1.2.11
msvc-1927
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> select * from t;
┌─────────────────────────────┬──────────────────┐
│              s              │        f         │
├─────────────────────────────┼──────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.00001 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.123   │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123   │
└─────────────────────────────┴──────────────────┘
sqlite> select s, printf('%!.16f', f) from t;
┌─────────────────────────────┬─────────────────────────────┐
│              s              │     printf('%!.16f', f)     │
├─────────────────────────────┼─────────────────────────────┤
│ 2016-06-13T09:36:34Z        │ 1465810594.0000085709584709 │
│ 2016-06-13T09:36:34.123Z    │ 1465810594.123001298072495  │
│ 2016-06-13T09:36:34.123456Z │ 1465810594.123001298072495  │
└─────────────────────────────┴─────────────────────────────┘
sqlite> select ulps((julianday('2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0, 1465810594.1219954490226107);
┌──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ ulps((julianday('2016-06-13T09:36:34.123Z') - 2440587.5) * 86400.0, 1465810594.1219954490226107) │
├──────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 4219.0                                                                                           │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

I have made no changes to date.c which would account for this.

(18) By Keith Medcalf (kmedcalf) on 2020-11-11 20:56:12 in reply to 17 [link] [source]

This is somewhat fascinating because according to my calculations the number of milliseconds since the julian epoch (the iJD value) should be 464269060799999 for the greatest round-trippable datetime ('9999-12-31 23:59:59.999'). This value can be exactly represented in a double precision floating point value (it requires 49 bits of the 53 available significand bits). Thus all lesser values should also be exactly represented.

Similarly the divisor 86400000 to convert from milliseconds since the epoch to days since the epoch can also be exactly represented in a double precision floating point value.

Dividing the former by the later should result in an "correctly rounded" result within 1 ULP, if the IEEE-754 computation rules are being followed.

Since this appears to not be the case, I can only conclude that the arithmetic computations (division) are somehow defectively implemented (apparently by the GCC 5.2.0 compiler being used).

The JulianDay value at the top of the round trippable range should have a machine precision of 0.08047 milliseconds, or well within the millisecond accuracy of the source iJD.

(16) By ddevienne on 2020-11-07 00:52:20 in reply to 13 [link] [source]

(cannot edit my post, didn't pass moderation yet). Here's what that official package says:

sqlite> .version
SQLite 3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f
zlib version 1.2.11
gcc-5.2.0

(14.1) By Keith Medcalf (kmedcalf) on 2020-11-06 23:32:28 edited from 14.0 in reply to 12 [link] [source]

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.

(4) By punkish on 2020-03-17 17:54:15 in reply to 1 [link] [source]

how about

sqlite> SELECT strftime('%s', a) +  strftime('%f', a) - strftime('%S', a) FROM
   ...> (SELECT '2016-06-13T09:36:34.123Z' a);
1465810594.123
sqlite>

(6) By anonymous on 2020-03-17 20:21:13 in reply to 1 [link] [source]

If it is OK to use an extension, then you could use an extension which calls the xCurrentTimeInt64 method of the VFS. (If it is unavailable for some reason, then call xCurrentTime instead and make the conversion yourself.)

(8) By Keith Medcalf (kmedcalf) on 2020-03-26 19:39:12 in reply to 6 [link] [source]

This would return a volatile time. The builtin datetime functions return statement stable datetime values.

If you write a function that calls the VFS method to get the "CurrentTime" you will get the "CurrentTime", that is the exact nanosecond as which the call was made.

If you use one of the builtin datetime functions, then the datetime will remain constant throughout execution of that statement.