MySQL TIMESTAMPADD() Function
The MySQL TIMESTAMPADD() function adds a specified interval to a date or datetime value and then returns the modified value. The unit for the interval is specified by the unit argument, which can be one of the following values: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both can be used.
Syntax
TIMESTAMPADD(unit, interval, datetime)
Parameters
datetime |
Required. Specify a date or datetime value to be modified. |
interval |
Required. Specify the value of the interval to add. Both positive and negative values are allowed. |
unit |
Required. Specify the unit type of interval to add. It can be one of the following values:
|
Return Value
Returns the modified date/datetime value.
Example 1:
The example below shows the usage of TIMESTAMPADD() function.
mysql> SELECT TIMESTAMPADD(DAY, 10, '2019-10-25'); Result: '2019-11-04' mysql> SELECT TIMESTAMPADD(SQL_TSI_DAY, 10, '2019-10-25'); Result: '2019-11-04' mysql> SELECT TIMESTAMPADD(HOUR, 2, '2019-10-25 08:10:25'); Result: '2019-10-25 10:10:25' mysql> SELECT TIMESTAMPADD(HOUR, -2, '2019-10-25 08:10:25'); Result: '2019-10-25 06:10:25' mysql> SELECT TIMESTAMPADD(MINUTE, 30, '2019-10-25 08:10:25'); Result: '2019-10-25 08:40:25' mysql> SELECT TIMESTAMPADD(SECOND, -10, '2019-10-25 08:10:25'); Result: '2019-10-25 08:10:15' mysql> SELECT TIMESTAMPADD(WEEK, 1, '2019-10-25 08:10:25'); Result: '2019-11-01 08:10:25' mysql> SELECT TIMESTAMPADD(MONTH, -2, '2019-10-25'); Result: '2019-08-25' mysql> SELECT TIMESTAMPADD(QUARTER, 2, '2019-10-25'); Result: '2020-04-25' mysql> SELECT TIMESTAMPADD(YEAR, 2, '2019-10-25'); Result: '2021-10-25' mysql> SELECT TIMESTAMPADD(MICROSECOND, 10, '2019-10-25 08:10:25'); Result: '2019-10-25 08:10:25.000010'
Example 2:
Consider a database table called EmployeeLogin with the following records:
| EmpID | Name | Login Stamp | Expected Logout Stamp |
|---|---|---|---|
| 1 | John | 2019-10-25 09:20:38 | 2019-10-25 17:20:38 |
| 2 | Marry | 2019-10-25 09:21:05 | 2019-10-25 17:21:05 |
| 3 | Jo | 2019-10-25 09:24:35 | 2019-10-25 17:24:35 |
| 4 | Kim | 2019-10-25 09:25:24 | 2019-10-25 17:25:24 |
| 5 | Ramesh | 2019-10-25 09:27:16 | 2019-10-25 17:27:16 |
To insert a new record in this table, the following statement can be used.
INSERT INTO EmployeeLogin VALUES (6, 'Suresh', NOW(), TIMESTAMPADD(HOUR, 8, NOW())); -- see the result SELECT * FROM EmployeeLogin;
This will produce a result similar to:
| EmpID | Name | Login Stamp | Expected Logout Stamp |
|---|---|---|---|
| 1 | John | 2019-10-25 09:20:38 | 2019-10-25 17:20:38 |
| 2 | Marry | 2019-10-25 09:21:05 | 2019-10-25 17:21:05 |
| 3 | Jo | 2019-10-25 09:24:35 | 2019-10-25 17:24:35 |
| 4 | Kim | 2019-10-25 09:25:24 | 2019-10-25 17:25:24 |
| 5 | Ramesh | 2019-10-25 09:27:16 | 2019-10-25 17:27:16 |
| 6 | Suresh | 2019-10-25 09:28:19 | 2019-10-25 17:28:19 |
❮ MySQL Functions
