How SUBDATE() Works in MariaDB

In MariaDB, SUBDATE() is a built-in date and time function that subtracts an amount from a given date.

It allows you to change a date by specifying the date, the unit to subtract, and the amount to subtract. You can pass a negative amount in order to add to the date, instead of subtracting from it.

SUBDATE() also has a shortcut syntax that allows you to specify the days to subtract.

Syntax

The SUBDATE() function has two syntaxes.

Syntax 1:

SUBDATE(expr,days)

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

Syntax 2:

SUBDATE(date,INTERVAL expr unit)

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

When using this syntax, SUBDATE() is a synonym for DATE_SUB().

Example – Syntax 1

Here’s an example of using the first syntax:

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

Result:

+--------------------------+
| SUBDATE('2021-05-01', 1) |
+--------------------------+
| 2021-04-30               |
+--------------------------+

We can also include the time portion if required:

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

Result:

+-----------------------------------+
| SUBDATE('2021-05-01 10:00:00', 1) |
+-----------------------------------+
| 2021-04-30 10:00:00               |
+-----------------------------------+

Here are two alternative ways of doing the same thing:

SELECT 
    DATE_SUB('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-04-30 10:00:00 | 2021-04-30 10:00:00 |
+---------------------+---------------------+

Example – Syntax 2

Here’s an example of using the second syntax:

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

Result:

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

This syntax allows us to subtract other units from the date (i.e. not just the days). Here, I subtracted an hour from the date, but I could just as easily have subtracted minutes, seconds, months, days, years, etc. Examples later.

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

SELECT 
    DATE_SUB('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 09:00:00 | 2021-05-31 09:00:00 |
+---------------------+---------------------+

Negative Intervals

Providing a negative interval adds that amount to the date.

Example:

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

Result:

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

Other Units

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

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

Result (using vertical output):

       YEAR: 2020-05-01 10:00:00
      MONTH: 2021-04-01 10:00:00
        DAY: 2021-04-30 10:00:00
       HOUR: 2021-05-01 09:00:00
     MINUTE: 2021-05-01 09:59:00
     SECOND: 2021-05-01 09:59:59
MICROSECOND: 2021-05-01 09:59:59.999999

Composite Units

Here’s an example that uses composite units:

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

Result:

+---------------------+---------------------+---------------------+
| YEAR_MONTH          | HOUR_SECOND         | DAY_MINUTE          |
+---------------------+---------------------+---------------------+
| 2020-03-01 10:00:00 | 2021-05-01 08:34:25 | 2021-05-01 08:30:00 |
+---------------------+---------------------+---------------------+

Null Dates

Passing null for the date returns null:

SELECT SUBDATE(null, INTERVAL 1 YEAR);

Result:

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

Missing Argument

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

SELECT SUBDATE();

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