Menu

SQLite timediff() Function

Updated on

SQLite timediff(A, B) returns a human-readable calendar difference that can also be used as a date/time modifier. It describes the interval that must be applied to B to reach A. For example, this query returns 1 when the modifier reconstructs A from B:

SELECT datetime('2023-02-15') =
       datetime('2023-03-15', timediff('2023-02-15', '2023-03-15')) AS reconstructs_a;

reconstructs_a
--------------
1

timediff() accepts exactly two time-values and no modifiers. Use ISO-8601 text values or Julian day numbers. It does not accept Unix timestamps with a separate 'unixepoch' modifier.

Syntax

timediff(A, B)

The returned text has the form (+|-)YYYY-MM-DD HH:MM:SS.SSS. The month and day fields are calendar-aware, so the same result can represent different numbers of elapsed days.

Example: calendar difference

SELECT timediff('2023-02-15', '2023-03-15') AS feb_to_mar,
       timediff('2023-03-15', '2023-04-15') AS mar_to_apr;

feb_to_mar                 mar_to_apr
-------------------------  -------------------------
-0000-01-00 00:00:00.000  -0000-01-00 00:00:00.000

Both intervals are one calendar month, even though the first spans 28 days and the second spans 31 days. Use julianday() or unixepoch() when you need numeric elapsed-time differences instead of a calendar interval.

For example, subtracting Julian day numbers gives the exact day counts for these dates:

SELECT julianday('2023-03-15') - julianday('2023-02-15') AS feb_to_mar_days,
       julianday('2023-04-15') - julianday('2023-03-15') AS mar_to_apr_days;

feb_to_mar_days  mar_to_apr_days
---------------  --------------
28.0             31.0

Use the result as a modifier

Because timediff() returns a modifier, it can be applied to the second time-value to reconstruct the first:

SELECT datetime(
    '2023-03-15',
    timediff('2023-02-15', '2023-03-15')
) AS reconstructed;

reconstructed
-------------------
2023-02-15 00:00:00

timediff() was added in SQLite 3.43.0. See the SQLite date and time function documentation for all accepted time-value formats and modifiers.