How the DATE_ADD() function works in Mariadb?

The DATE_ADD() function is a built-in function in Mariadb that adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value.

Posted on

The DATE_ADD() function is a built-in function in Mariadb that adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value. This function is often used to calculate future or past dates based on a given date.

Syntax

The syntax of the DATE_ADD() function is as follows:

DATE_ADD(date, INTERVAL expr unit)

The date argument is the date or datetime value to which the interval is added. The INTERVAL expr unit argument is the time interval to be added, where expr is a numeric expression and unit is a keyword that specifies the unit of the interval, such as DAY, MONTH, YEAR, etc.

Examples

Example 1: Adding days to a date

The following example shows how to use the DATE_ADD() function to add 10 days to a date value.

SELECT DATE_ADD('2023-12-17', INTERVAL 10 DAY);

The output is:

+-----------------------------------------+
| DATE_ADD('2023-12-17', INTERVAL 10 DAY) |
+-----------------------------------------+
| 2023-12-27                              |
+-----------------------------------------+

This means that the result is 2023-12-27, which is 10 days after 2023-12-17.

Example 2: Subtracting months from a datetime

The following example shows how to use the DATE_ADD() function to subtract 3 months from a datetime value.

SELECT DATE_ADD('2023-12-17 20:14:30', INTERVAL -3 MONTH);

The output is:

+----------------------------------------------------+
| DATE_ADD('2023-12-17 20:14:30', INTERVAL -3 MONTH) |
+----------------------------------------------------+
| 2023-09-17 20:14:30                                |
+----------------------------------------------------+

This means that the result is 2023-09-17 20:14:30, which is 3 months before 2023-12-17 20:14:30.

There are some other functions in Mariadb that are related to the DATE_ADD() function. Here are some of them:

  • DATE_SUB(): This function is equivalent to the DATE_ADD() function with a negative interval. It subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value.
  • ADDDATE(): This function is a synonym for the DATE_ADD() function. It adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value.
  • SUBDATE(): This function is a synonym for the DATE_SUB() function. It subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value.

Conclusion

The DATE_ADD() function is a useful function in Mariadb that adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value. It can be used with different units of the interval, such as DAY, MONTH, YEAR, etc. It can also be used to calculate future or past dates based on a given date. There are some other functions in Mariadb that are related to the DATE_ADD() function, such as DATE_SUB(), ADDDATE(), and SUBDATE(). These functions can be used to perform similar operations with different syntaxes.