How the DATE_SUB() function works in Mariadb?

The DATE_SUB() function is a built-in function in Mariadb that subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value.

Posted on

The DATE_SUB() function is a built-in function in Mariadb that subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value. This function is often used to calculate future or past dates based on a given date.

Syntax

The syntax of the DATE_SUB() function is as follows:

DATE_SUB(date, INTERVAL expr unit)

The date argument is the date or datetime value from which the interval is subtracted. The INTERVAL expr unit argument is the time interval to be subtracted, where expr is a numeric expression and unit is a keyword that specifies the unit of the interval, such as DAY, MONTH, YEAR, etc.

Examples

Example 1: Subtracting days from a date

The following example shows how to use the DATE_SUB() function to subtract 10 days from a date value.

SELECT DATE_SUB('2023-12-17', INTERVAL 10 DAY);

The output is:

+-----------------------------------------+
| DATE_SUB('2023-12-17', INTERVAL 10 DAY) |
+-----------------------------------------+
| 2023-12-07                              |
+-----------------------------------------+

This means that the result is 2023-12-07, which is 10 days before 2023-12-17.

Example 2: Adding months to a datetime

The following example shows how to use the DATE_SUB() function to add 3 months to a datetime value.

SELECT DATE_SUB('2023-12-17 20:14:30', INTERVAL -3 MONTH);

The output is:

+----------------------------------------------------+
| DATE_SUB('2023-12-17 20:14:30', INTERVAL -3 MONTH) |
+----------------------------------------------------+
| 2024-03-17 20:14:30                                |
+----------------------------------------------------+

This means that the result is 2023-03-17 20:14:30, which is 3 months after 2023-12-17 20:14:30.

There are some other functions in Mariadb that are related to the DATE_SUB() function. Here are some of them:

  • DATE_ADD(): This function is equivalent to the DATE_SUB() function with a negative interval. It adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value.
  • SUBDATE(): This function is a synonym for the DATE_SUB() function. It subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value.
  • ADDDATE(): This function is a synonym for the DATE_ADD() function. It adds a specified time interval to a date or datetime value and returns the result as a new date or datetime value.

For example, the following query shows the equivalence between the DATE_SUB() function, the DATE_ADD() function, the SUBDATE() function, and the ADDDATE() function.

SELECT DATE_SUB('2023-12-17', INTERVAL 10 DAY) AS "DATE_SUB 10",
       DATE_ADD('2023-12-17', INTERVAL -10 DAY) AS "DATE_ADD -10",
       SUBDATE('2023-12-17', INTERVAL 10 DAY) AS "SUBDATE 10",
       ADDDATE('2023-12-17', INTERVAL -10 DAY) AS "ADDDATE 10";

The output is:

+-------------+--------------+------------+------------+
| DATE_SUB 10 | DATE_ADD -10 | SUBDATE 10 | ADDDATE 10 |
+-------------+--------------+------------+------------+
| 2023-12-07  | 2023-12-07   | 2023-12-07 | 2023-12-07 |
+-------------+--------------+------------+------------+

As you can see, all the functions return the same value, which is 2023-12-07.

Conclusion

The DATE_SUB() function is a useful function in Mariadb that subtracts a specified time interval from a date or datetime value and returns the result as a new date or datetime value. It can be used with different units of the interval, such as DAY, MONTH, YEAR, etc. It can also be used to calculate future or past dates based on a given date. There are some other functions in Mariadb that are related to the DATE_SUB() function, such as DATE_ADD(), SUBDATE(), and ADDDATE(). These functions can be used to perform similar operations with different syntaxes.