2 Ways to Return the Unix Timestamp in SQLite

Posted on by Ian

The unix timestamp is the number of seconds since 1970-01-01 00:00:00 UTC. SQLite provides us with a couple of ways to get the unix timestamp.

The STRFTIME() Function

The STRFTIME() function returns a date and time value in the specified format. We can use the %s format string substitution to return the number of seconds since 1970-01-01 00:00:00 UTC (i.e. the unix timestamp):

SELECT STRFTIME('%s');

Result:

1646695406

The UNIXEPOCH() Function

The UNIXEPOCH() function is specifically designed to return the a unix timestamp. This function was introduced in SQLite 3.38.0 (released 2nd February 2022), so it will only work if you’re using SQLite 3.38.0 or higher:

SELECT UNIXEPOCH();

Result:

1646695411

We can see that the results are similar, but there’s obviously a difference due to the amount of time it took me to run each example.

Combined

Just to be sure, here they are again. Except, this time I run them both within the same SELECT statement:

SELECT 
    UNIXEPOCH(),
    STRFTIME('%s');

Result:

UNIXEPOCH()  STRFTIME('%s')
-----------  --------------
1646695558   1646695558