Subtract Hours from a Time Value in SQLite

In SQLite, we can use the TIME() function to subtract one or more hours 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 hour');

Result:

15:30:45

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

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

SELECT 
    TIME('16:30:45', '-8 hour') AS hour,
    TIME('16:30:45', '-8 hours') AS hours;

Result:

hour      hours   
--------  --------
08:30:45  08:30:45

We can also subtract hours based on a number of minutes:

SELECT TIME('16:30:45', '-60 minutes');

Result:

15:30:45

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

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

Result:

2050-08-21 15:30:45