sqlite: how to select current_timestamp as millisecond value since 1/1/1970

Question

sqlite> select typeof(date('now'));
text
sqlite> select typeof(current_time);
text
sqlite> select typeof(current_date);
text

How to select the current date/time as an integer value of milliseconds since Jan/1/1970?

score 4 · Accepted Answer · answered May 14 '18 at 18:26

4

strftime() gives you only seconds:

SELECT strftime('%s', 'now') * 1000;

answered May 14 '18 at 18:26

CL.

173,858
17
217
259

easier to understand. – eastwater May 14 '18 at 19:06
Still only seconds precision, so you're just adding 000 to the end. – eezing Feb 17 '23 at 16:21

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

From SQLite doc:

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):
SELECT (julianday('now') - 2440587.5)*86400.0; 

SELECT (julianday('now') - 2440587.5)*86400.0 * 1000;

DBFiddle Demo

edited Jun 20 '20 at 09:12

Community

1
1

answered May 14 '18 at 18:26

Lukasz Szozda

162,964
23
234
275

2

Too late but why not to an explanation for newcomers? _2440587.5 is the julian day at 1/1/1970 0:00 UTC_. 86400 is _seconds in a day_ – Ataberk Dec 10 '18 at 19:12

sqlite: how to select current_timestamp as millisecond value since 1/1/1970

2 Answers2