MariaDB DATE_ADD() Function

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

MariaDB DATE_ADD() Syntax

This is the syntax of the MariaDB DATE_ADD() function:

DATE_ADD(date, INTERVAL value unit)

Parameters

date

Required. The date to process.

value

Required. The time/date interval. Both positive and negative numbers are allowed.

unit

Required. The unit of the time/date interval.

The unit of the time/date interval can be one of the following values:

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1.

Return value

The MariaDB DATE_ADD() function adds the specified time interval to the specified date/time and returns a new date/time. The return value of the DATE_ADD() function is related to the parameters:

  • If the date argument is of DATE type and the interval is YEAR, MONTH or DAY, returns DATE.
  • If the date argument is of DATE type and the interval is HOURS, MINUTES or SECONDS, returns DATETIME.
  • If the date parameter is of DATETIME type, returns DATETIME.
  • If the date argument is of TIME type and the interval is YEAR, MONTH or DAY, returns DATETIME.
  • Otherwise returns a string.

MariaDB DATE_ADD() Examples

Example 1 - Add

SELECT
  DATE_ADD('2023-01-06', INTERVAL 1 DAY),
  DATE_ADD('2023-01-06', INTERVAL 1 MONTH),
  DATE_ADD('2023-01-06', INTERVAL 1 YEAR)\G

Output:

  DATE_ADD('2023-01-06', INTERVAL 1 DAY): 2023-01-07
DATE_ADD('2023-01-06', INTERVAL 1 MONTH): 2023-02-06
 DATE_ADD('2023-01-06', INTERVAL 1 YEAR): 2024-01-06

Example 2 - Subtract

MariaDB DATE_ADD() allows to subtract a given interval by providing a negative value:

SELECT
  DATE_ADD('2023-01-06', INTERVAL -1 DAY),
  DATE_ADD('2023-01-06', INTERVAL -1 MONTH),
  DATE_ADD('2023-01-06', INTERVAL -1 YEAR)\G

Output:

  DATE_ADD('2023-01-06', INTERVAL -1 DAY): 2023-01-05
DATE_ADD('2023-01-06', INTERVAL -1 MONTH): 2022-12-06
 DATE_ADD('2023-01-06', INTERVAL -1 YEAR): 2022-01-06

Example 3 - Datetime

MariaDB DATE_ADD() function supports you to work with datetime values:

SELECT DATE_ADD('2023-01-06 10:11:12', INTERVAL 1 HOUR);

Output:

+--------------------------------------------------+
| DATE_ADD('2023-01-06 10:11:12', INTERVAL 1 HOUR) |
+--------------------------------------------------+
| 2023-01-06 11:11:12                              |
+--------------------------------------------------+

Likewise, you can use other time units, such as a year:

SELECT DATE_ADD('2023-01-06 10:11:12', INTERVAL 1 YEAR);

Output:

+--------------------------------------------------+
| DATE_ADD('2023-01-06 10:11:12', INTERVAL 1 YEAR) |
+--------------------------------------------------+
| 2024-01-06 10:11:12                              |
+--------------------------------------------------+

Conclusion

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