MySQL SUBTIME() Function

In MySQL, the SUBTIME() function subtracts a specified time interval from another specified time and returns the result.

`SUBTIME()` Syntax

Here is the syntax of MySQL SUBTIME() function:

SUBTIME(timeExpr1, timeExpr2)

Parameters

timeExpr1: Required. It is a datetime or time expression.
timeExpr2: Required. It is a time expression. It can be positive or negative.

Return value

The SUBTIME() function subtracts timeExpr2 from timeExpr1 and returns the result.

The SUBTIME() function determines the type of the return value according to the following rules:

If the timeExpr1 parameter is a dynamic type, the function returns a TIME value.
Otherwise, the function returns the same data type as the first argumenttimeExpr1.

`SUBTIME()` Examples

Here are some examples of the SUBTIME() function.

Example 1

Subtract 10 seconds from the specified time:

SELECT
    SUBTIME('2020-10-10 10:10:10', 10),
    SUBTIME('10:10:10', 10)\G

SUBTIME('2020-10-10 10:10:10', 10): 2020-10-10 10:10:00
           SUBTIME('10:10:10', 10): 10:10:00

Example 2

Subtract 1 minute from the specified time:

SELECT
    SUBTIME('10:10:10', 100),
    SUBTIME('10:10:10', '100'),
    SUBTIME('10:10:10', '0:01:00')\G

      SUBTIME('10:10:10', 100): 10:09:10
    SUBTIME('10:10:10', '100'): 10:09:10
SUBTIME('10:10:10', '0:01:00'): 10:09:10

Example 3

Subtract and add 1 hour, 10 minutes, 10 seconds, 10 microseconds to the specified time.

SELECT
    SUBTIME('10:00:00', '01:10:10.000010'),
    SUBTIME('10:00:00', '-01:10:10.000010'),
    ADDTIME('10:00:00', '01:10:10.000010')\G

 SUBTIME('10:00:00', '01:10:10.000010'): 08:49:49.999990
SUBTIME('10:00:00', '-01:10:10.000010'): 11:10:10.000010
 ADDTIME('10:00:00', '01:10:10.000010'): 11:10:10.000010

Here, the minus sign in front of '-01:10:10.000010' means subtracting '01:10:10.000010' from '10:00:00'.

SUBTIME() Syntax