Tutorial Gateway

MySQL TIMESTAMPADD Function

by

MySQL TIMESTAMPADD function is the one of the Date Function, which is used to add the user specified intervals to a given date or DateTime.

In this article, we will show you, How to use this MySQL TIMESTAMP ADD function to add intervals to a given date expression with an example.

MySQL TIMESTAMPADD Syntax

The basic syntax of the TIMESTAMPADD() Function in MySQL 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 that you want to add to the TIMESTAMP.
  • Unit: Accepts values from Microsecond to Year

TIP: I suggest you to refer to Date Function article to understand the Units after the Interval.

MySQL TIMESTAMPADD function Example 1

The below shown queries helps you understand the use of this MySQL TIMESTAMP ADD function.

Here, First, we are adding 111222 Microseconds to 2019-01-31 date. Next, we added 30 Seconds to date. Within the third statement, we added 25 Minutes to the given Date (2019-01-31).

SELECT TIMESTAMPADD(MICROSECOND, 111222, '2019-01-31');

SELECT TIMESTAMPADD(SECOND, 59, '2019-01-31');

SELECT TIMESTAMPADD(MINUTE, 25, '2019-01-31');

OUTPUT

MySQL TIMESTAMPADD Function 1

MySQL TIMESTAMPADD Function 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');

OUTPUT

MySQL TIMESTAMPADD Function 2

MySQL TIMESTAMPADD Example 3

In this example, we will show you, how to use this MySQL Timestamp add function on a table data. Here, we are adding 100 Hours to HireDate column and retuning the Timestamp.

SELECT EmpID, 
       FirstName,
       LastName,
       Occupation,
       YearlyIncome,
       Sales,
       HireDate,
       TIMESTAMPADD(HOUR, 100, HireDate) AS 'TIMESTAMPADD Example'
 FROM `MySQL Tutorial`.customer;

OUTPUT

MySQL TIMESTAMPADD Function 3

Thank You for Visiting Our Blog