Search code examples
sqlitetimestampunix-timestamp

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
Solution

  • 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.