MariaDB SUBTIME() Function

In MariaDB, SUBTIME() is a built-in function that subtracts a time from a time or datetime expression.

MariaDB SUBTIME() Syntax

This is the syntax of the MariaDB 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.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SUBTIME'.

Return value

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

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

If the timeExpr1 parameter is of dynamic type, the SUBTIME() function return values is of TIME type. Otherwise, the data type returned by the SUBTIME() function is the same as the first parameter.

MariaDB SUBTIME() Examples

Example 1

Subtract 10 seconds from the specified time:

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

Output:

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

Output:

      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

Adds or subtracts 1 hour, 10 minutes, 10 seconds, and 10 microseconds to the specified time.

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

Output:

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

Conclusion

In MariaDB, SUBTIME() is a built-in function that adds a specified time interval to a specified time and returns the result.