Unix timestamp in sqlite?

Question

How do you obtain the current timestamp in Sqlite? current_time, current_date, current_timestamp both return formatted dates, instead of a long.

sqlite> insert into events (timestamp) values (current_timestamp);
sqlite> insert into events (timestamp) values (current_date);
sqlite> insert into events (timestamp) values (current_time);
sqlite> select * from events;
1|2010-09-11 23:18:38
2|2010-09-11
3|23:18:51

What I want:

4|23234232

score 129 · Accepted Answer · answered Sep 11 '10 at 23:35

129

The docs mention this method:

SELECT strftime('%s', 'now');
1284248196

And this one which includes the fractional part:

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

Both represent Unix Time, the number of seconds passed since January 1, 1970.

answered Sep 11 '10 at 23:35

Daniel Vassallo

337,827
72
505
443

7

Still handy info after 10 years! – axello Feb 23 '20 at 09:26
1

The first one may need casting to a number before numeric comparisons! For example, a quick and dirty `1*strftime('%s', 'now')` might be enough, or you can use the proper `CAST( strftime('%s', 'now') AS INT )` syntax. – biziclop Jul 07 '22 at 14:27

score 2 · Answer 2 · answered Aug 20 '22 at 23:59

2

SQLite now has unixepoch function that returns a unix timestamp (https://www.sqlite.org/lang_datefunc.html)

However right now I don't recommend it because some things like Sqlitebrowser and Entity Framework (6.0.8) don't seem to support it yet. Right now it's still better to use old strftime from accepted answer

answered Aug 20 '22 at 23:59

Mikhail Kriseev

37
7

1

More specifically "That command wasn't added until 3.38 (2022-02-22)." (see https://sqlite.org/forum/forumpost/5f8af1c00129da37) – Mathieu Rey Oct 12 '22 at 06:27
1

The correct way to do now, is `unixepoch('now')` – dessalines Aug 21 '23 at 13:04

score 0 · Answer 3 · answered Feb 23 '21 at 15:37

select strftime('%W'),date('now'),date(),datetime(),strftime('%s', 'now');

result in

strftime('%W')  -   date('now')  -  date()   -  datetime()   -     strftime('%s', 'now')
    08       -      2021-02-23   -  2021-02-23 - 2021-02-23  15:35:12-  1614094512

Unix timestamp in sqlite?

3 Answers3