MariaDB SUBDATE() Function

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

SUBDATE() is the opposite of ADDDATE().

MariaDB SUBDATE() Syntax

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

SUBDATE(date, days)
SUBDATE(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. Units 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 supply 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 SUBDATE() function subtracts the specified time interval from the specified date/time and returns a new date/time. The return value of the SUBDATE() function is related to the parameters:

  • If the date parameter is of DATE type and the interval is YEAR, MONTH or DAY, returns DATE.
  • If the date parameter is of DATE type and the interval is HOURS, MINUTES or SECONDS, returns DATETIME.
  • If the date parameter is of DATETIME type, returns it DATETIME.
  • If the date parameter is of TIME type and the interval is YEAR, MONTH or DAY, returns DATETIME.
  • Otherwise returns a string.

MariaDB SUBDATE() Examples

Example 1 - Subtract days

To subtract the number of days from a given date, use the following statement with MariaDB SUBDATE() function:

SELECT SUBDATE('2023-01-05', 5);

Output:

+--------------------------+
| SUBDATE('2023-01-05', 5) |
+--------------------------+
| 2022-12-31               |
+--------------------------+

MariaDB SUBDATE() allows you to subtract days from a datetime value:

SELECT SUBDATE('2023-01-05 10:11:12', 5);

Output:

+-----------------------------------+
| SUBDATE('2023-01-05 10:11:12', 5) |
+-----------------------------------+
| 2022-12-31 10:11:12               |
+-----------------------------------+

Example 2 - INTERVAL

MariaDB SUBDATE() allows providing a value of INTERVAL type:

SELECT
  SUBDATE('2023-01-05', INTERVAL 10 DAY),
  SUBDATE('2023-01-05', INTERVAL 10 HOUR),
  SUBDATE('2023-01-05 10:11:12', INTERVAL 10 HOUR),
  SUBDATE('2023-01-05 10:11:12', INTERVAL 10 MINUTE)\G

Output:

            SUBDATE('2023-01-05', INTERVAL 10 DAY): 2022-12-26
           SUBDATE('2023-01-05', INTERVAL 10 HOUR): 2023-01-04 14:00:00
  SUBDATE('2023-01-05 10:11:12', INTERVAL 10 HOUR): 2023-01-05 00:11:12
SUBDATE('2023-01-05 10:11:12', INTERVAL 10 MINUTE): 2023-01-05 10:01:12

Example 3 - Adding days

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

SELECT
  SUBDATE('2023-01-05', -5),
  SUBDATE('2023-01-05', INTERVAL -5 DAY)\G

Output:

             SUBDATE('2023-01-05', -5): 2023-01-10
SUBDATE('2023-01-05', INTERVAL -5 DAY): 2023-01-10

Conclusion

In MariaDB, the SUBDATE() function subtracts the specified time interval from the specified date/time and returns the result.