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