In SQLite, we can use the TIME() function to subtract one or more seconds from a time value.

For datetime values, we can use the DATETIME() function.

Example

Here’s an example that uses the TIME() function:

SELECT TIME('16:30:45', '-1 second');

Result:

16:30:44

If we wanted to add the amount, we could replace - with +, or omit it altogether.

We can specify the seconds in plural or non-plural form. In other words, second is equivalent to seconds:

SELECT 
    TIME('16:30:45', '-45 second') AS second,
    TIME('16:30:45', '-45 seconds') AS seconds;

Result:

second    seconds 
--------  --------
16:30:00  16:30:00

Specified in Minutes

Given there are 60 seconds in a minute, if the number of seconds to subtract is in 60 second increments, we can subtract the equivalent amount in minutes.

Therefore, the following are equivalent:

SELECT 
    TIME('16:30:45', '60 seconds') AS "60 seconds",
    TIME('16:30:45', '1 minute') AS "1 minute";

Result:

60 seconds  1 minute
----------  --------
16:31:45    16:31:45

The DATETIME() Function

This example uses the DATETIME() function to subtract seconds from a datetime value:

SELECT DATETIME('2050-08-21 16:30:45', '-45 seconds');

Result:

2050-08-21 16:30:00