MySQL TIMESTAMPADD function is one of the Date methods used to add the user-specified intervals to a given date or DateTime. Let us see how to use this TIMESTAMP ADD function to add intervals to a given date expression with an example.
MySQL TIMESTAMPADD Syntax
The basic syntax of the MySQL TIMESTAMPADD Function is as shown below:
TIMESTAMPADD(Unit, INTERVAL_expression, DateTime_expression);
- DateTime: Accepts either DateTime value or date.
- INTERVAL_Expression: You have to specify the value you want to add to the TIMESTAMP.
- Unit: Accepts values from Microsecond to Year
MySQL TIMESTAMPADD function Example
The below-shown queries help you understand the use of this TIMESTAMP ADD method. Here, First, we are adding 111222 Microseconds to 2019-01-31. Next, we added 30 Seconds to date. Within the third statement, we added 25 Minutes to the given (2019-01-31).
SELECT TIMESTAMPADD(MICROSECOND, 111222, '2019-01-31');
SELECT TIMESTAMPADD(SECOND, 59, '2019-01-31');
SELECT TIMESTAMPADD(MINUTE, 25, '2019-01-31');
TIP: I suggest you refer to the Date methods article in MySQL to understand the Units after the Interval.
TIMESTAMPADD Example 2
In this example, First, we are adding 48 Hours. Next, we added 48 weeks to a given Date expression. Within the third statement of MySQL TIMESTAMPADD function, we added 14 Years.
SELECT TIMESTAMPADD(HOUR, 48, '2019-01-31');
SELECT TIMESTAMPADD(WEEK, 48, '2019-01-31');
SELECT TIMESTAMPADD(YEAR, 14, '2019-01-31');
In this example, we show you how to use this Timestamp add function on table data. Here, we are adding 100 Hours to the HireDate column and retuning the Timestamp.
SELECT EmpID, FirstName, LastName, Occupation, YearlyIncome, Sales, HireDate, TIMESTAMPADD(HOUR, 100, HireDate) AS 'TIMESTAMPADD Example' FROM customer;