How the ADDTIME() function works in Mariadb?

MariaDB’s ADDTIME() function is a versatile tool used to perform arithmetic on time values.

Posted on

The MariaDB ADDTIME() function is a versatile tool used to perform arithmetic on time values. It allows you to add a specified time interval to a given time or datetime expression, returning the result as a new time or datetime value.

Syntax

The syntax for the MariaDB ADDTIME() function is as follows:

ADDTIME(expr1, expr2)

expr1 is a time or datetime expression to which you want to add time, and expr2 is the time expression that represents the interval you wish to add.

Examples

Adding Hours to a Time

To demonstrate adding hours to a time, consider the following example:

SELECT ADDTIME('09:00:00', '2:00:00');

+--------------------------------+
| ADDTIME('09:00:00', '2:00:00') |
+--------------------------------+
| 11:00:00                       |
+--------------------------------+

This statement adds 2 hours to 9 AM, resulting in 11 AM.

Adding Minutes to a Datetime

This example shows how to add minutes to a datetime value:

SELECT ADDTIME('2023-03-16 09:00:00', '00:30:00');

+--------------------------------------------+
| ADDTIME('2023-03-16 09:00:00', '00:30:00') |
+--------------------------------------------+
| 2023-03-16 09:30:00                        |
+--------------------------------------------+

Here, 30 minutes are added to the datetime, resulting in a half-hour increment.

Subtracting Time

You can also subtract time by using a negative interval:

SELECT ADDTIME('09:00:00', '-0:30:00');

+---------------------------------+
| ADDTIME('09:00:00', '-0:30:00') |
+---------------------------------+
| 08:30:00                        |
+---------------------------------+

This subtracts 30 minutes from 9 AM, giving us 8:30 AM.

Adding Seconds

Adding seconds to a time is just as straightforward:

SELECT ADDTIME('09:00:00', '00:00:30');

+---------------------------------+
| ADDTIME('09:00:00', '00:00:30') |
+---------------------------------+
| 09:00:30                        |
+---------------------------------+

This adds 30 seconds to 9 AM.

Handling Overflow

When adding time results in an overflow, ADDTIME() handles it gracefully:

SELECT ADDTIME('23:59:00', '0:02:00');

+--------------------------------+
| ADDTIME('23:59:00', '0:02:00') |
+--------------------------------+
| 24:01:00                       |
+--------------------------------+

This goes beyond midnight, showing how ADDTIME() deals with overflow by extending past the 24-hour mark.

Here are a few functions related to MariaDB’s ADDTIME():

  • MariaDB SUBTIME() function is used to subtract time from a datetime or time expression.
  • MariaDB TIMEDIFF() function calculates the difference between two time or datetime expressions.
  • MariaDB DATE_ADD() function adds a specified time interval to a date.

Conclusion

The ADDTIME() function in MariaDB is a powerful feature for manipulating time and datetime values. Whether you’re adding hours, minutes, or seconds, or even handling more complex time arithmetic, ADDTIME() provides a simple and effective solution for your SQL queries. Remember to consider the context of your data and the desired outcome when using this function to ensure accurate results.