How to Subtract a Day from a Date in MariaDB

by

MariaDB provides several ways to perform arithmetic on dates. This includes adding or subtracting a day (or many days) from a given date.

Here’s an example of subtracting a day from a date in MariaDB.

The SUBDATE() Function

The SUBDATE() function allows you to subtract a certain number of days from a date.

The easiest way to do this is to provide the date, followed by the number of days you’d like to subtract.

Example:

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

Result:

+--------------------------+
| SUBDATE('2021-05-21', 1) |
+--------------------------+
| 2021-05-20               |
+--------------------------+

Multiple Days

If you need to subtract more than one day, just use that number instead of 1. 

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

Result:

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

In this case we returned the date, minus 30 days. This also brought the date back to the previous month.

A More Verbose Syntax

The SUBDATE() function also has a more verbose syntax, where you can specify the unit to subtract from the date. For example, you can specify days, weeks, months, years, etc.

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

Result:

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

Note that when subtracting multiple days, the DAY keyword remains non-plural:

SELECT SUBDATE('2021-05-01', INTERVAL 10 DAY);

Result:

+----------------------------------------+
| SUBDATE('2021-05-01', INTERVAL 10 DAY) |
+----------------------------------------+
| 2021-04-21                             |
+----------------------------------------+

The Subtraction Operator (-)

Another way to subtract days from a date is to use the subtraction operator (-), also known as the minus operator.

Example:

SELECT '2021-05-01' - INTERVAL 90 DAY;

Result:

+--------------------------------+
| '2021-05-01' - INTERVAL 90 DAY |
+--------------------------------+
| 2021-01-31                     |
+--------------------------------+

The Addition Operator (+)

You can alternatively use the addition operator (+) along with a negative amount.

Example:

SELECT '2021-05-01' + INTERVAL -90 DAY;

Result:

+---------------------------------+
| '2021-05-01' + INTERVAL -90 DAY |
+---------------------------------+
| 2021-01-31                      |
+---------------------------------+

Other Ways to Subtract Days from Dates

Here are some other approaches you could use to subtract one or more days from a date:

  • The DATE_SUB() function (the SUBDATE() function used in the above example is a synonym for DATE_SUB() when used with the same syntax).
  • The DATE_ADD() function (providing a negative amount will subtract that amount from the date).
  • The ADDDATE() function (providing a negative amount will subtract that amount from the date).