MySQL - TIMESTAMPADD() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL TIMESTAMPADD() function is used to add the specified time interval to a date time or, date expression.

Syntax

Following is the syntax of the above function –

TIMESTAMPADD(unit,interval,datetime_expr)

Where,

• unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

The unit can be mixed values as: SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

• Interval is the expression representing the time interval to be added.

• datetime_expr is the date or date-time expression to which you need to add the time interval.

Example 1

Following example demonstrates the usage of the TIMESTAMPADD() function –

mysql> SELECT TIMESTAMPADD(DAY, 32, '2019-05-02');
+-------------------------------------+
| TIMESTAMPADD(DAY, 32, '2019-05-02') |
+-------------------------------------+
| 2019-06-03                          |
+-------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT TIMESTAMPADD(YEAR,4,'2008-01-02');
+-----------------------------------+
| TIMESTAMPADD(YEAR,4,'2008-01-02') |
+-----------------------------------+
| 2012-01-02                        |
+-----------------------------------+
1 row in set (0.00 sec)

Example 3

In the following example we are passing DATETIME value for date –

mysql> SELECT TIMESTAMPADD(HOUR,225,'2018-05-23 20:40:32.88558');
+----------------------------------------------------+
| TIMESTAMPADD(HOUR,225,'2018-05-23 20:40:32.88558') |
+----------------------------------------------------+
| 2018-06-02 05:40:32.885580                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

Example 4

We can also pass negative values as arguments to this function –

mysql> SELECT TIMESTAMPADD(DAY,-20,'2015-09-05');
+------------------------------------+
| TIMESTAMPADD(DAY,-20,'2015-09-05') |
+------------------------------------+
| 2015-08-16                         |
+------------------------------------+
1 row in set (0.11 sec)

mysql> SELECT TIMESTAMPADD(MONTH,-12,'1995-11-15');
+--------------------------------------+
| TIMESTAMPADD(MONTH,-12,'1995-11-15') |
+--------------------------------------+
| 1994-11-15                           |
+--------------------------------------+
1 row in set (0.00 sec)

Example 5

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query adds 14 years to the entities of the Date_Of_Birth column —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, TIMESTAMPADD(YEAR ,14,Date_Of_Birth) FROM MyPlayers;
+------------+------------+---------------+-------------+--------------------------------------+
| First_Name | Last_Name  | Date_Of_Birth | Country     | TIMESTAMPADD(YEAR ,14,Date_Of_Birth) |
+------------+------------+---------------+-------------+--------------------------------------+
| Shikhar    | Dhawan     | 1981-12-05    | India       | 1995-12-05                           |
| Jonathan   | Trott      | 1981-04-22    | SouthAfrica | 1995-04-22                           |
| Kumara     | Sangakkara | 1977-10-27    | Srilanka    | 1991-10-27                           |
| Virat      | Kohli      | 1988-11-05    | India       | 2002-11-05                           |
| Rohit      | Sharma     | 1987-04-30    | India       | 2001-04-30                           |
| Ravindra   | Jadeja     | 1988-12-06    | India       | 2002-12-06                           |
| James      | Anderson   | 1982-06-30    | England     | 1996-06-30                           |
+------------+------------+---------------+-------------+--------------------------------------+
7 rows in set (0.25 sec)

Example 6

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query adds 365 days to the dates of the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, TIMESTAMPADD(DAY,65,DispatchTimeStamp) FROM dispatches_data;
+-------------+--------------+---------------------+-------+----------------------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | TIMESTAMPADD(DAY,65,DispatchTimeStamp) |
+-------------+--------------+---------------------+-------+----------------------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  | 2019-07-08 15:02:45                    |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  | 2019-08-30 14:13:12                    |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  | 2020-02-10 07:50:37                    |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  | 2018-05-25 16:00:45                    |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  | 2019-03-05 10:49:27                    |
+-------------+--------------+---------------------+-------+----------------------------------------+
5 rows in set (0.07 sec)

Example 7

Following example demonstrates the usage WEEK and QUARTER units available in the TIMESTAMPADD() function –

mysql> SELECT TIMESTAMPADD(WEEK,3,'2021-03-22');
+-----------------------------------+
| TIMESTAMPADD(WEEK,3,'2021-03-22') |
+-----------------------------------+
| 2021-04-12                        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPADD(QUARTER,3,'2021-03-22');
+--------------------------------------+
| TIMESTAMPADD(QUARTER,3,'2021-03-22') |
+--------------------------------------+
| 2021-12-22                           |
+--------------------------------------+
1 row in set (0.00 sec)