42

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?

Takkun
  • 6,131
  • 16
  • 52
  • 69
  • TIMESTAMP is number of seconds that passed since 1st Jan 1970 so guess you will need another field for miliseconds – fsw Jul 10 '13 at 15:35
  • POSIX time is defined as elapsed milliseconds since midnight 01-Jan-1970 UTC. I would say that using milliseconds as a timestamp is ok as long as you're sure it's POSIX time (i.e. in UTC). Otherwise, the use of milliseconds for local timestamps is very unusual and you should use other numeric temporal formats instead. See: http://stackoverflow.com/questions/32670064/how-do-i-get-posix-time-utc-from-a-serial-value-local-date-time-with-the-java – scottb Sep 25 '15 at 19:47

5 Answers5

53

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')));
Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • With Oracle background the `%f` looks weird by itself, it's the same as Oracle's `TO_DATE(... SS.FFF')` – TWiStErRob Aug 11 '14 at 23:02
  • `...%H:%M:%f` that's weird, why isn't it `...%H:%M:%S.%f` for seconds, and milliseconds? the `f` indicates seconds is a double truncated to 6 decimal places, rather than an int? ok – MetaStack Dec 16 '22 at 16:43
  • From https://www.sqlite.org/lang_datefunc.html, `%f fractional seconds: SS.SSS` – LS_ᴅᴇᴠ Dec 20 '22 at 10:57
30

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);
Ed J
  • 2,466
  • 4
  • 26
  • 20
Nikita Krupenko
  • 1,117
  • 1
  • 13
  • 17
  • 2
    Add ROUND to get more accurate thousandths: CAST(ROUND((julianday('now') - 2440587.5)*86400000) As INTEGER) – celoftis Oct 12 '18 at 20:11
14

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.

Michael
  • 9,060
  • 14
  • 61
  • 123
  • 1
    Can it produce a wrong result when the two `strftime` calls happen with a tiny delay and belong to different seconds? – algrid Apr 20 '22 at 16:29
  • 1
    @algrid A query is declarative, so the actual way the query is done is not specified; so in practice (in SQLite) it's not actually making multiple calls at different times. – Michael Apr 20 '22 at 18:04
12

Here's a query that will generate a timestamp as a string with milliseconds:

 select strftime("%Y-%m-%d %H:%M:%f", "now");

If you're really bent on using a numeric representation, you could use:

select julianday("now");
Zoe
  • 27,060
  • 21
  • 118
  • 148
David Marx
  • 8,172
  • 3
  • 45
  • 66
  • 2
    fyi, julianday() is not natively comparable with anything based on epoch time, whether seconds or milliseconds. You'll have to convert everywhere in your code. – Ed J May 13 '18 at 19:16
4

The accepted answer only gives you UTC. If you need a local time instead of UTC, use this:

strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')
Adamy
  • 2,789
  • 3
  • 27
  • 25