The MySQL DATE_SUB function is a powerful date and time manipulation function that allows you to subtract a specified time interval from a given date or datetime expression. This function is particularly useful when you need to perform calculations involving date and time values in your MySQL queries.
Syntax
Here is the basic syntax of the DATE_SUB function:
DATE_SUB(date, INTERVAL expr unit)
date: This is the initial date or datetime expression from which you want to subtract the specified time interval.
expr: This is the numeric value representing the amount you want to subtract.
unit: This specifies the unit of the time interval, such as SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR.
Example
Let’s look at a few examples to better understand how to use the DATE_SUB function:
Subtracting days from a date
SELECT DATE_SUB('2022-01-01', INTERVAL 7 DAY) AS new_date;
This query subtracts 7 days from the date ‘2022-01-01’, resulting in a new date of ‘2021-12-25’.
Subtracting hours from a datetime
SELECT DATE_SUB('2022-01-01 12:00:00', INTERVAL 3 HOUR) AS new_datetime;
Here, 3 hours are subtracted from the datetime ‘2022-01-01 12:00:00’, resulting in a new datetime of ‘2022-01-01 09:00:00’.
Subtracting months from a date
SELECT DATE_SUB('2022-01-01', INTERVAL 2 MONTH) AS new_date;
This query subtracts 2 months from the date ‘2022-01-01’, resulting in a new date of ‘2021-11-01’.
Subtracting seconds from a datetime
SELECT DATE_SUB('2022-01-01 00:00:10', INTERVAL 5 SECOND) AS new_datetime;
In this example, 5 seconds are subtracted from the datetime ‘2022-01-01 00:00:10’, resulting in a new datetime of ‘2022-01-01 00:00:05’.
The DATE_SUB function is flexible and allows you to perform a wide range of date and time manipulations in MySQL queries. It is a valuable tool for handling temporal data and conducting calculations based on date and time intervals.