MariaDB TIMESTAMPADD() Function

The MariaDB 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.

SELECT TIMESTAMPADD(DAY, 10, '2019-10-25');
Result: '2019-11-04'

SELECT TIMESTAMPADD(SQL_TSI_DAY, 10, '2019-10-25');
Result: '2019-11-04'

SELECT TIMESTAMPADD(HOUR, 2, '2019-10-25 08:10:25');
Result: '2019-10-25 10:10:25'

SELECT TIMESTAMPADD(HOUR, -2, '2019-10-25 08:10:25');
Result: '2019-10-25 06:10:25'

SELECT TIMESTAMPADD(MINUTE, 30, '2019-10-25 08:10:25');
Result: '2019-10-25 08:40:25'

SELECT TIMESTAMPADD(SECOND, -10, '2019-10-25 08:10:25');
Result: '2019-10-25 08:10:15'

SELECT TIMESTAMPADD(WEEK, 1, '2019-10-25 08:10:25');
Result: '2019-11-01 08:10:25'

SELECT TIMESTAMPADD(MONTH, -2, '2019-10-25');
Result: '2019-08-25'

SELECT TIMESTAMPADD(QUARTER, 2, '2019-10-25');
Result: '2020-04-25'

SELECT TIMESTAMPADD(YEAR, 2, '2019-10-25');
Result: '2021-10-25'

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

❮ MariaDB Functions