Unix timestamp in milliseconds in SQLite

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:

select unixepoch('subsec');

Displaying them as human readable strings

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.

Why not multiply seconds by 1000?

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.

How it works

The 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.

Wikipedia says:

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.

Crucially, the 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.

There are 86400 seconds in a day.

So... 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.

Alternative approach using fractional seconds

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:

%f fractional seconds: SS.SSS

For example 18.412 for 18s and 412ms past the minute.

They suggested this:

select 1000*(strftime('%s','now')+mod(strftime('%f','now'),1));

The 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.

The 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

Unix timestamp in milliseconds in SQLite

Displaying them as human readable strings

Why not multiply seconds by 1000?

How it works

Alternative approach using fractional seconds

Related