The DATE_ADD function in MySQL is a powerful tool for manipulating date and time values within your queries. It allows you to add a specified time interval to a given date or datetime expression, resulting in a new date or datetime value. This function is particularly useful when you need to perform calculations or adjustments involving time intervals, such as adding days, hours, minutes, or seconds to a date.
Syntax
Here is the basic syntax of the DATE_ADD function:
DATE_ADD(date_expression, INTERVAL value unit);
date_expression: This is the initial date or datetime value to which you want to add the time interval.
value: This is the numeric value that represents the amount of time you want to add.
unit: This specifies the time unit (e.g., DAY, HOUR, MINUTE, SECOND) in which you want to add the specified value.
Example
Here are a few examples to illustrate how to use the DATE_ADD function:
Add days to a date
SELECT DATE_ADD('2024-01-01', INTERVAL 7 DAY) AS new_date;
This query adds 7 days to the date ‘2024-01-01’ and returns the result as ‘2024-01-08’.
Add hours to a datetime
SELECT DATE_ADD('2024-01-01 12:00:00', INTERVAL 3 HOUR) AS new_datetime;
This query adds 3 hours to the datetime ‘2024-01-01 12:00:00’ and returns the result as ‘2024-01-01 15:00:00’.
Add minutes to the current date and time
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE) AS new_datetime;
This query adds 30 minutes to the current date and time and returns the result.
Add seconds to a specific datetime
SELECT DATE_ADD('2024-01-01 10:30:00', INTERVAL 45 SECOND) AS new_datetime;
This query adds 45 seconds to the datetime ‘2024-01-01 10:30:00’ and returns the updated datetime.
It’s important to note that the DATE_ADD function doesn’t modify the original date or datetime value; instead, it produces a new value with the specified adjustments. Additionally, the INTERVAL keyword is used to specify the time unit and value to be added.
In summary, the DATE_ADD function is a valuable tool in MySQL for performing date and time calculations, making it easier to handle temporal data in a flexible and precise manner within your SQL queries.