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.