SQL Tutorial SQL Advanced SQL Database SQL References

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:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:20:38
2Marry2019-10-25 09:21:052019-10-25 17:21:05
3Jo2019-10-25 09:24:352019-10-25 17:24:35
4Kim2019-10-25 09:25:242019-10-25 17:25:24
5Ramesh2019-10-25 09:27:162019-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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:20:38
2Marry2019-10-25 09:21:052019-10-25 17:21:05
3Jo2019-10-25 09:24:352019-10-25 17:24:35
4Kim2019-10-25 09:25:242019-10-25 17:25:24
5Ramesh2019-10-25 09:27:162019-10-25 17:27:16
6Suresh2019-10-25 09:28:192019-10-25 17:28:19

❮ MySQL Functions