How ADDTIME() Works in MariaDB

In MariaDB, ADDTIME() is a built-in date and time function that adds an amount to a time or datetime expression.

It allows you to change a time or datetime expression by passing that expression, as well as a time expression to add to that expression. You can pass a negative amount if you need to subtract from the time expression.

Syntax

The syntax goes like this:

ADDTIME(expr1,expr2)

Where expr1 is a time or datetime expression, and expr2 is a time expression.

Example

Here’s an example:

SELECT ADDTIME('10:00:00', '02:30:45');

Result:

+---------------------------------+
| ADDTIME('10:00:00', '02:30:45') |
+---------------------------------+
| 12:30:45                        |
+---------------------------------+

Datetime Expressions

The first argument can also be a datetime expression:

SELECT ADDTIME('2021-05-01 10:00:00', '22:30:45');

Result:

+--------------------------------------------+
| ADDTIME('2021-05-01 10:00:00', '22:30:45') |
+--------------------------------------------+
| 2021-05-02 08:30:45                        |
+--------------------------------------------+

In this case, the time added was enough to push the date forward to the next day, and this is reflected in the result.

Time Expressions in a Looser Format

MariaDB allows assignment of times in looser formats, such as dropping leading zeros and providing a value for the day.

Therefore, we can do this:

SELECT ADDTIME('2021-05-01 10:00:00', '366 1:1:1.123456');

Result:

+----------------------------------------------------+
| ADDTIME('2021-05-01 10:00:00', '366 1:1:1.123456') |
+----------------------------------------------------+
| 2022-05-02 11:01:01.123456                         |
+----------------------------------------------------+

Negative Intervals

Providing a negative interval for the day portion subtracts that amount from the time/datetime.

Example:

SELECT ADDTIME('2021-05-01 10:00:00', '-366 1:1:1.123456');

Result:

+-----------------------------------------------------+
| ADDTIME('2021-05-01 10:00:00', '-366 1:1:1.123456') |
+-----------------------------------------------------+
| 2020-04-30 08:58:58.876544                          |
+-----------------------------------------------------+

Null Arguments

If any argument is null, the result is null:

SELECT 
    ADDTIME('10:00:00', null),
    ADDTIME(null, '02:30:45'),
    ADDTIME(null, null);

Result:

+---------------------------+---------------------------+---------------------+
| ADDTIME('10:00:00', null) | ADDTIME(null, '02:30:45') | ADDTIME(null, null) |
+---------------------------+---------------------------+---------------------+
| NULL                      | NULL                      | NULL                |
+---------------------------+---------------------------+---------------------+

Missing Argument

Calling ADDTIME() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT ADDTIME();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ADDTIME'