SQLite Current Timestamp with Milliseconds?

Question

I am storing a timestamp field in a SQLite3 column as TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP and I was wondering if there was any way for it to include milliseconds in the timestamp as well?

Answer 1

Instead of CURRENT_TIMESTAMP, use (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) so that your column definition become:

TIMESTAMP DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))

For example:

CREATE TABLE IF NOT EXISTS event
(when_ts DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')));

Answer 2

To get number of milliseconds since epoch you can use julianday() with some additional calculations:

-- Julian time to Epoch MS     
SELECT CAST((julianday('now') - 2440587.5)*86400000 AS INTEGER); 

Answer 3

The following method doesn't require any multiplies or divides and should always produce the correct result, as multiple calls to get 'now' in a single query should always return the same result:

SELECT strftime('%s','now') || substr(strftime('%f','now'),4);

The generates the number of seconds and concatenates it to the milliseconds part from the current second+millisecond.