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'