In MariaDB, TIMESTAMPADD()
is a built-in date and time function that adds an integer expression interval to a given date or datetime expression.
Syntax
The syntax goes like this:
TIMESTAMPADD(unit,interval,datetime_expr)
Where unit
is one of the following values:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
The units can optionally have a prefix of SQL_TSI_
.
Example
Here’s an example to demonstrate:
SELECT TIMESTAMPADD(DAY, 10, '2030-02-01');
Result:
+-------------------------------------+ | TIMESTAMPADD(DAY, 10, '2030-02-01') | +-------------------------------------+ | 2030-02-11 | +-------------------------------------+
Datetime Values
Here’s an example of passing a datetime value:
SELECT TIMESTAMPADD(HOUR, 10, '2030-02-01 10:30:45');
Result:
+-----------------------------------------------+ | TIMESTAMPADD(HOUR, 10, '2030-02-01 10:30:45') | +-----------------------------------------------+ | 2030-02-01 20:30:45 | +-----------------------------------------------+
Adding a SQL_TSI_
Prefix
The unit can include a SQL_TSI_
prefix if required:
SELECT TIMESTAMPADD(SQL_TSI_YEAR, 10, '2030-02-01');
Result:
+----------------------------------------------+ | TIMESTAMPADD(SQL_TSI_YEAR, 10, '2030-02-01') | +----------------------------------------------+ | 2040-02-01 | +----------------------------------------------+
Microseconds
Here’s an example that adds microseconds:
SELECT TIMESTAMPADD(MICROSECOND, 123456, '2030-02-01 10:30:45');
Result:
+----------------------------------------------------------+ | TIMESTAMPADD(MICROSECOND, 123456, '2030-02-01 10:30:45') | +----------------------------------------------------------+ | 2030-02-01 10:30:45.123456 | +----------------------------------------------------------+
Negative Intervals
Negative intervals are valid:
Example:
SELECT TIMESTAMPADD(YEAR, -10, '2030-02-01');
Result:
+---------------------------------------+ | TIMESTAMPADD(YEAR, -10, '2030-02-01') | +---------------------------------------+ | 2020-02-01 | +---------------------------------------+
Current Date
We can pass NOW()
as the datetime argument to use the current date and time:
SELECT
NOW(),
TIMESTAMPADD(DAY, 10, NOW());
Result:
+---------------------+------------------------------+ | NOW() | TIMESTAMPADD(DAY, 10, NOW()) | +---------------------+------------------------------+ | 2021-05-28 09:58:56 | 2021-06-07 09:58:56 | +---------------------+------------------------------+
Missing Argument
Calling TIMESTAMPADD()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT TIMESTAMPADD();
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
And another example:
SELECT TIMESTAMPADD(10, '2020-12-09');
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10, '2020-12-09')' at line 1