Menu

SQLite unixepoch() Function

Updated on

SQLite unixepoch() converts a supported time-value to seconds since 1970-01-01 00:00:00 UTC. It returns an integer by default; with the subsec modifier (SQLite 3.42.0 or later), it returns a floating-point value with fractional seconds.

Syntax

unixepoch(time_value [, modifier, ...])

The time-value is optional; if omitted, SQLite uses now. Accepted inputs include ISO-8601 text and Julian day numbers. For a numeric Unix timestamp, add the unixepoch modifier immediately after the value:

SELECT unixepoch(1748528160, 'unixepoch');

The auto modifier can interpret a numeric input as either a Julian day number or Unix timestamp, but Unix timestamps from the first 63 days of 1970 are ambiguous. Prefer unixepoch when you know the input format.

Modifiers are applied from left to right. For example, '+1 day' shifts the value by a day, while 'subsec' retains milliseconds. See the SQLite date and time documentation for the full modifier list and ordering rules.

Convert a time value

SELECT unixepoch('1970-01-02 00:00:00');

unixepoch('1970-01-02 00:00:00')
--------------------------------
86400

With subsec, fractional seconds are returned:

SELECT unixepoch('2023-03-15 12:00:00.500', 'subsec');

unixepoch('2023-03-15 12:00:00.500', 'subsec')
-----------------------------------------------
1678881600.5

Calculate elapsed seconds

Subtract two Unix timestamps to calculate elapsed seconds:

SELECT unixepoch('2024-03-01') - unixepoch('2024-02-01') AS elapsed_seconds;

elapsed_seconds
---------------
2505600

For a human-readable calendar interval, use timediff(). For fractional elapsed days, use julianday().