How ADDDATE() Works in MariaDB

In MariaDB, ADDDATE() is a built-in date and time function that performs date arithmetic.

It allows you to change a date by specifying the date, the unit to add, and the amount to add. You can pass a negative amount if you need to subtract the date by a certain interval.

ADDDATE() also has a shortcut syntax that allows you to add a given number of days to the date.

Syntax

The ADDDATE() function has two syntaxes.

Syntax 1:

ADDDATE(expr,days)

Where expr is the date, and days is the number of days to add.

Syntax 2:

ADDDATE(date,INTERVAL expr unit)

Where date is the date to change, expr is the amount to add, and unit is the unit to add (e.g. second, minute, etc).

When using this syntax, ADDDATE() is a synonym for DATE_ADD().

Example – Syntax 1

Here’s an example of using the first syntax:

SELECT ADDDATE('2021-05-01', 1);

Result:

+--------------------------+
| ADDDATE('2021-05-01', 1) |
+--------------------------+
| 2021-05-02               |
+--------------------------+

We can also include the time portion if required:

SELECT ADDDATE('2021-05-01 10:00:00', 1);

Result:

+-----------------------------------+
| ADDDATE('2021-05-01 10:00:00', 1) |
+-----------------------------------+
| 2021-05-02 10:00:00               |
+-----------------------------------+

Here are two alternative ways of doing the same thing:

SELECT 
    DATE_ADD('2021-05-01 10:00:00', INTERVAL 1 DAY) AS "Result 1",
    '2021-05-01 10:00:00' + INTERVAL 1 DAY AS "Result 2";

Result:

+---------------------+---------------------+
| Result 1            | Result 2            |
+---------------------+---------------------+
| 2021-05-02 10:00:00 | 2021-05-02 10:00:00 |
+---------------------+---------------------+

Example – Syntax 2

Here’s an example of using the second syntax:

SELECT ADDDATE('2021-05-31 10:00:00', INTERVAL 1 HOUR);

Result:

+-------------------------------------------------+
| ADDDATE('2021-05-31 10:00:00', INTERVAL 1 HOUR) |
+-------------------------------------------------+
| 2021-05-31 11:00:00                             |
+-------------------------------------------------+

This syntax allows us to add other units to the date (i.e. not just the days). Here, I added an hour to the date, but I could just as easily have added minutes, seconds, months, days, years, etc. Continue reading for examples.

Here are two alternative methods for achieving the same outcome as the above example:

SELECT 
    DATE_ADD('2021-05-31 10:00:00', INTERVAL 1 HOUR) AS "Result 1",
    '2021-05-31 10:00:00' + INTERVAL 1 HOUR AS "Result 2";

Result:

+---------------------+---------------------+
| Result 1            | Result 2            |
+---------------------+---------------------+
| 2021-05-31 11:00:00 | 2021-05-31 11:00:00 |
+---------------------+---------------------+

Negative Intervals

Providing a negative interval subtracts that amount from the date.

Example:

SELECT ADDDATE('2021-05-31 10:00:00', INTERVAL -1 HOUR);

Result:

+--------------------------------------------------+
| ADDDATE('2021-05-31 10:00:00', INTERVAL -1 HOUR) |
+--------------------------------------------------+
| 2021-05-31 09:00:00                              |
+--------------------------------------------------+

Other Units

Here’s an example that adds an interval of 1 to the various date and time units:

SELECT 
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 YEAR) AS YEAR,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MONTH) AS MONTH,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 DAY) AS DAY,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 HOUR) AS HOUR,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MINUTE) AS MINUTE,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 SECOND) AS SECOND,
    ADDDATE('2021-05-01 10:00:00', INTERVAL 1 MICROSECOND) AS MICROSECOND;

Result (using vertical output):

       YEAR: 2022-05-01 10:00:00
      MONTH: 2021-06-01 10:00:00
        DAY: 2021-05-02 10:00:00
       HOUR: 2021-05-01 11:00:00
     MINUTE: 2021-05-01 10:01:00
     SECOND: 2021-05-01 10:00:01
MICROSECOND: 2021-05-01 10:00:00.000001

Composite Units

Here’s an example that uses composite units:

SELECT 
    ADDDATE('2021-05-01 10:00:00', INTERVAL '1:2' YEAR_MONTH) AS "YEAR_MONTH",
    ADDDATE('2021-05-01 10:00:00', INTERVAL '1:25:35' HOUR_SECOND) AS "HOUR_SECOND",
    ADDDATE('2021-05-01 10:00:00', INTERVAL '1:30' DAY_MINUTE) AS "DAY_MINUTE";

Result:

+---------------------+---------------------+---------------------+
| YEAR_MONTH          | HOUR_SECOND         | DAY_MINUTE          |
+---------------------+---------------------+---------------------+
| 2022-07-01 10:00:00 | 2021-05-01 11:25:35 | 2021-05-01 11:30:00 |
+---------------------+---------------------+---------------------+

Null Dates

Passing null for the date returns null:

SELECT ADDDATE(null, INTERVAL 1 YEAR);

Result:

+--------------------------------+
| ADDDATE(null, INTERVAL 1 YEAR) |
+--------------------------------+
| NULL                           |
+--------------------------------+

Missing Argument

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

SELECT ADDDATE();

Result:

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