CODEWITHSUNDEEP
sqlsqlite
Takkun
Takkun

Reputation: 6361

SQLite Current Timestamp with Milliseconds?

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?

Upvotes: 51

Views: 52193

Answers (6)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Update[2025-01-20]

Modern versions of sqlite allow a subsec modifier for the datetime(), time(), and unixepoch() functions. Thus, OP could now do this:

TIMESTAMP DATETIME DEFAULT(datetime('subsec'))

Or for an integer column named "createdAt" with default unix epoch as integer milliseconds:

createdAt  INTEGER DEFAULT(unixepoch('subsec') * 1000)

[Original answer]

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')));

Upvotes: 64

Matt
Matt

Reputation: 74670

Another epoch ms method for sqlite > 3.42.0 which added subsecond

SELECT unixepoch('subsecond') * 1000;
1703231951563.0

The value is a real, but in an integer column the value will be cast.

CREATE TABLE "items" (
  "created_at" integer default(unixepoch('subsecond') * 1000) not null
);

As in cast(x as int)

SELECT cast(unixepoch('subsecond') * 1000 as int);
1703231955581

Upvotes: 2

Adamy
Adamy

Reputation: 2849

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')

Upvotes: 5

David Marx
David Marx

Reputation: 8558

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");

Upvotes: 12

Michael
Michael

Reputation: 9402

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.

Upvotes: 14

Nikita Krupenko
Nikita Krupenko

Reputation: 1175

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);

Upvotes: 31

Related Questions