I wanted to retrieve the time in milliseconds since the Unix epoch in SQLite.
Fetching seconds since the epoch is easy:
select strftime('%s', 'now');
Prior to SQLite 3.42.0 (released in May 2023) milliseconds were much more complex.
After some digging around, I found the following recipe:
select cast( (julianday('now') - 2440587.5) * 86400 * 1000 as integer )
Try these both here.
In SQLite 3.42.0 and higher you can do this instead:
This fragment of SQL turns them back into a readable UTC value:
select strftime('%Y-%m-%d %H:%M:%S', :timestamp_ms / 1000, 'unixepoch')
The output looks like this:
2023-04-09 05:04:24 - try that out here.
An alternative way of getting milliseconds since the epoch is to do this:
select strftime('%s', 'now') * 1000
The problem with this is that seconds there is an integer - so if I multiply by 1000 I'll always get a number ending in ...000 - but I want millisecond precision on my timestamps here, so that's not useful.
julianday('now') function returns the number of days since the "Julian epoch". The Julian epoch is 12:00 noon on January 1, 4713 BC in the proleptic Julian calendar.
The Julian day is the continuous count of days since the beginning of the Julian period, and is used primarily by astronomers, and in software for easily calculating elapsed days between two events (e.g. food production date and sell by date).
The significance of 4713 BC? It was chosen as a date before any existing historical record.
julianday function returns a floating point number of days. This differs from
strftime('%s', 'now') which returns an integer number of seconds.
2440587.5 is the number of days between the Julian epoch and the Unix epoch.
86400 seconds in a day.
julianday('now') - 2440587.5 is the number of days since the Unix epoch, and multiplying that by
86400 gives us the floating point number of seconds since the Unix epoch.
Finally we multiply by
1000 because we want milliseconds, not seconds - and we cast the result to an integer because that's the type of number I want to store.
See sqlite-history/issues/6 for background information on why I needed this.
mgr on the SQLite Forum pointed out an alternative way of solving this, using the
%f format code for
strftime() which returns the number of seconds as a floating point number of seconds at millisecond accuracy:
For example 18.412 for 18s and 412ms past the minute.
They suggested this:
mod() function isn't available on all SQLite installations though. I found this pattern works instead:
select (1000 * (strftime('%s', 'now'))) + cast( substr( strftime('%f', 'now'), instr(strftime('%f', 'now'), '.') + 1 ) as integer ) as timestamp_ms
Try that here.
substr('18.413', instr('18.413', '.') + 1) part returns just the characters after the first
. character, which are then cast to integer to get the milliseconds fraction of that second. These are added to
1000 * the unix timestamp in seconds.
Created 2023-04-08T21:52:58-07:00, updated 2023-05-18T14:20:17-07:00 · History · Edit