MySQL TIMESTAMPADD() function

TIMESTAMPADD() function

MySQL TIMESTAMPADD() adds time value with a date or datetime value. It's a powerful tool for performing date and time calculations, allowing you to manipulate temporal data in various ways.

The unit for the interval as mentioned should be one of the following : FRAC_SECOND(microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR.

This function is useful in -

• TIMESTAMPADD() allows you to customize time calculations based on specific requirements or business logic.
• The function is valuable for calculating dates in the future or the past, depending on the provided time interval.
• TIMESTAMPADD() can be used to handle time zone conversions and adjustments, ensuring that the calculations are accurate regardless of the time zone.
• It's useful in batch processing scenarios where a large set of records needs to be updated or analyzed based on date and time intervals.
• TIMESTAMPADD() is crucial for time series analysis, where you need to generate a sequence of dates based on a specific pattern.
• It supports the manipulation of temporal data, which is essential in scenarios like financial calculations, project planning, and scheduling.
• It supports data aging and archiving processes, allowing you to determine when data should be considered "old" based on a given interval.

Syntax:

TIMESTAMPADD(unit,interval,datetime_expr);

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example:

The following statement will return a date value after adding 2 months with the specified date 2009-05-18.

Code:

SELECT TIMESTAMPADD(MONTH,2,'2009-05-18');

Output:

mysql> SELECT TIMESTAMPADD(MONTH,2,'2009-05-18');
+------------------------------------+
| TIMESTAMPADD(MONTH,2,'2009-05-18') |
+------------------------------------+
| 2009-07-18                         | 
+------------------------------------+
1 row in set (0.00 sec)

Example : TIMESTAMPADD() function in WEEK

The following statement will return a date value after adding 1 week with the specified date 2009-05-18.

Code:

SELECT TIMESTAMPADD(WEEK,1,'2009-05-18');

Output:

mysql> SELECT TIMESTAMPADD(WEEK,1,'2009-05-18');
+-----------------------------------+
| TIMESTAMPADD(WEEK,1,'2009-05-18') |
+-----------------------------------+
| 2009-05-25                        | 
+-----------------------------------+
1 row in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: TIMEDIFF()
Next: TIMESTAMPDIFF()