MariaDB DATE_SUB() Function

In MariaDB, DATE_SUB() is a built-in function that subtracts the specified time interval from the specified date/time and returns the new date/time.

MariaDB DATE_SUB() Syntax

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

DATE_SUB(date, INTERVAL value unit)

Parameters

date

Required. The date to process.

days

Required. The number of days to subtract from date.

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_SUB() function adds the specified time interval to the specified date/time and returns a new date/time. The return value of the DATE_SUB() 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_SUB() Examples

Example 1 - Subtract

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

Output:

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

Example 2 - Adding

MariaDB DATE_SUB() allows to add a given interval by supplying negative values:

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

Output:

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

Example 3 - datetime

The MariaDB DATE_SUB() function support you to work with datetime values:

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

Output:

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

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

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

Output:

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

Conclusion

In MariaDB, DATE_SUB() is a built-in function that subtracts the specified time interval from the specified date/time and returns the result.