CODEWITHSUNDEEP
sqlitetimestampunix-timestamp
yayitswei
yayitswei

Reputation: 4687

Unix timestamp in sqlite?

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

Upvotes: 55

Views: 72043

Answers (3)

Mikhail Kriseev
Mikhail Kriseev

Reputation: 89

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

Upvotes: 6

Hakan
Hakan

Reputation: 613

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

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344521

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.

Upvotes: 141

Related Questions