In MySQL, you can use the SUBTIME() function to subtract a time value from a time or datetime expression.
The way it works is, you provide two arguments; the first is a time or datetime value, and the second is a time value. The SUBTIME() function then subtracts the second argument from the first one and returns the result.
Syntax
The syntax goes like this:
SUBTIME(expr1,expr2)
Where expr1 can be either a time or datetime expression, and expr2 is a time expression.
And so expr2 is subtracted from expr1.
Example 1 – Basic Usage
Here’s an example to demonstrate.
SELECT SUBTIME('12:35:00', '1:30');
Result:
+-----------------------------+
| SUBTIME('12:35:00', '1:30') |
+-----------------------------+
| 11:05:00 |
+-----------------------------+
Example 2 – Subtracting Seconds
In this example, I also subtract a number of seconds from the time value.
SELECT SUBTIME('12:35:00', '1:30:30');
Result:
+--------------------------------+
| SUBTIME('12:35:00', '1:30:30') |
+--------------------------------+
| 11:04:30 |
+--------------------------------+
And we get the same result even if we omit the seconds part from the initial time argument.
SELECT SUBTIME('12:35', '1:30:30');
Result:
+-----------------------------+
| SUBTIME('12:35', '1:30:30') |
+-----------------------------+
| 11:04:30 |
+-----------------------------+
Example 3 – Fractional Seconds
You can also subtract the fractional seconds part.
SELECT SUBTIME('12:35:00.888888', '1:30:30.555555');
Result:
+----------------------------------------------+
| SUBTIME('12:35:00.888888', '1:30:30.555555') |
+----------------------------------------------+
| 11:04:30.333333 |
+----------------------------------------------+
Example 4 – Negative Results
It’s quite valid to end up with a negative value for your result.
SELECT SUBTIME('12:35:00', '20:30:30');
Result:
+---------------------------------+
| SUBTIME('12:35:00', '20:30:30') |
+---------------------------------+
| -07:55:30 |
+---------------------------------+
Example 5 – Subtract from a Datetime Value
In this example I subtract from a datetime value (as opposed to just a time value like in the previous examples).
SELECT SUBTIME('2021-01-01 12:35:00', '1:30:30');
Result:
+-------------------------------------------+
| SUBTIME('2021-01-01 12:35:00', '1:30:30') |
+-------------------------------------------+
| 2021-01-01 11:04:30 |
+-------------------------------------------+
In this case, the date part was unchanged, as the second argument wasn’t large enough to affect it.
In this next example, I increase the second argument so that it’s large enough to affect the date part.
SELECT SUBTIME('2021-01-01 12:35:00', '100:30:30');
Result:
+---------------------------------------------+
| SUBTIME('2021-01-01 12:35:00', '100:30:30') |
+---------------------------------------------+
| 2020-12-28 08:04:30 |
+---------------------------------------------+
As seen in this example, time values aren’t restricted to being less than 24 hours (they can range from -838:59:59 to 838:59:59.
However, you can also do stuff like this:
SELECT SUBTIME('2021-01-01 12:35:00', '4 4:30:30');
Result:
+---------------------------------------------+
| SUBTIME('2021-01-01 12:35:00', '4 4:30:30') |
+---------------------------------------------+
| 2020-12-28 08:04:30 |
+---------------------------------------------+
Example 6 – Limitations of the time Value
As mentioned, the time data type can range from -838:59:59 to 838:59:59. This means that you can’t subtract any more than that. It also means that the result can’t be outside of this range. For example, you can’t do this:
SELECT SUBTIME('12:35:00', '20000000:30:30');
Result:
+---------------------------------------+
| SUBTIME('12:35:00', '20000000:30:30') |
+---------------------------------------+
| -826:24:59 |
+---------------------------------------+
1 row in set, 1 warning (0.01 sec)
The result is completely wrong and MySQL displays a warning.
But it’s not just the second argument you need to be careful of. You’ll also run into the same issue if the first argument is initially a negative value:
SELECT SUBTIME('-800:35:00', '50:30:30');
Result:
+-----------------------------------+
| SUBTIME('-800:35:00', '50:30:30') |
+-----------------------------------+
| -838:59:59 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)