How the SUBDATE() function works in Mariadb?

The SUBDATE() function in MariaDB is used to subtract a specified time interval from a given date or datetime value. This function is particularly useful when you need to perform date calculations or manipulations based on a specific time interval.

Syntax

The syntax for the MariaDB SUBDATE() function is as follows:

SUBDATE(date, INTERVAL value interval_type)

• date: The date or datetime value from which the interval should be subtracted. This is a required parameter.
• INTERVAL value interval_type: This specifies the time interval to be subtracted from the date. The value is a numeric value, and the interval_type can be one of the following:
  • 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

The function returns a DATE or DATETIME value, depending on the input date value, after subtracting the specified time interval.

Examples

Example 1: Subtracting days from a date

This example demonstrates how to subtract a specified number of days from a date.

SELECT SUBDATE('2023-03-15', INTERVAL 5 DAY) AS result;

The following is the output:

+------------+
| result     |
+------------+
| 2023-03-10 |
+------------+

In this example, the SUBDATE() function subtracts 5 days from the date '2023-03-15', resulting in '2023-03-10'.

Example 2: Subtracting months from a date

This example shows how to subtract a specified number of months from a date.

SELECT SUBDATE('2023-03-15', INTERVAL 2 MONTH) AS result;

The following is the output:

+------------+
| result     |
+------------+
| 2023-01-15 |
+------------+

In this example, the SUBDATE() function subtracts 2 months from the date '2023-03-15', resulting in '2023-01-15'.

Example 3: Subtracting a combined interval from a datetime

The SUBDATE() function can also handle combined intervals, such as days and hours, or hours and minutes.

DROP TABLE IF EXISTS example;
CREATE TABLE example (datetime_col DATETIME);
INSERT INTO example (datetime_col) VALUES ('2023-03-15 10:30:00');

SELECT datetime_col,
       SUBDATE(datetime_col, INTERVAL 2 DAY) AS result_days,
       SUBDATE(datetime_col, INTERVAL 3 HOUR) AS result_hours,
       SUBDATE(datetime_col, INTERVAL '2 2:30' DAY_MINUTE) AS result_combined
FROM example;

The following is the output:

+---------------------+---------------------+---------------------+---------------------+
| datetime_col        | result_days         | result_hours        | result_combined     |
+---------------------+---------------------+---------------------+---------------------+
| 2023-03-15 10:30:00 | 2023-03-13 10:30:00 | 2023-03-15 07:30:00 | 2023-03-13 08:00:00 |
+---------------------+---------------------+---------------------+---------------------+

In this example, the first SUBDATE() call subtracts 2 days from the datetime value, the second call subtracts 3 hours, and the third call subtracts a combined interval of 2 days and 2 hours 30 minutes.

Example 4: Subtracting a year from a date

This example demonstrates how to subtract a year from a date.

SELECT SUBDATE('2023-03-15', INTERVAL 1 YEAR) AS result;

The following is the output:

+------------+
| result     |
+------------+
| 2022-03-15 |
+------------+

In this example, the SUBDATE() function subtracts 1 year from the date '2023-03-15', resulting in '2022-03-15'.

Example 5: Handling NULL values

If the date parameter or the interval value is NULL, the SUBDATE() function returns NULL.

DROP TABLE IF EXISTS example;
CREATE TABLE example (date_col DATE);
INSERT INTO example (date_col) VALUES ('2023-03-15'), (NULL);

SELECT date_col,
       SUBDATE(date_col, INTERVAL 5 DAY) AS result
FROM example;

The following is the output:

+------------+------------+
| date_col   | result     |
+------------+------------+
| 2023-03-15 | 2023-03-10 |
| NULL       | NULL       |
+------------+------------+

In this example, the second row returns NULL because the date_col value is NULL.

Related Functions

The following are some functions related to the MariaDB SUBDATE() function:

• MariaDB DATE_ADD() function is used to add a specified time interval to a date or datetime value.
• MariaDB DATEDIFF() function is used to calculate the difference between two dates in terms of days.
• MariaDB DATE_FORMAT() function is used to format a date or datetime value according to a specified format.
• MariaDB DATE_SUB() function is an alternative to SUBDATE() for subtracting a time interval from a date or datetime value.

Conclusion

The SUBDATE() function in MariaDB is a powerful tool for manipulating dates and times by subtracting a specified time interval. It supports various interval types, including seconds, minutes, hours, days, weeks, months, and years, as well as combined intervals. By understanding the syntax and usage of this function, you can perform date calculations and manipulations with ease in your SQL queries and applications.