TIMESTAMPADD() Examples – MySQL

Posted on by Ian

In MySQL, the TIMESTAMPADD() function allows you to add a specified amount of time to a date or datetime value. You specify the unit to add, as well as how many of that unit to add. It accepts three arguments, which are used for the initial value, the amount to add, and the unit to use.

Syntax

The syntax goes like this:

TIMESTAMPADD(unit,interval,datetime_expr)

Where unit is the unit to add, interval is how many of the units to add, and datetime_expr is the initial date or datetime value.

The unit argument can be either MICROSECOND SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The unit argument can also have a prefix of SQL_TSI_. For example, you could use either DAY or SQL_TSI_DAY.

Example 1 – Add a Day

In this example I add a day to the initial date.

SELECT TIMESTAMPADD(DAY, 1, '1999-12-31');

Result:

+------------------------------------+
| TIMESTAMPADD(DAY, 1, '1999-12-31') |
+------------------------------------+
| 2000-01-01                         |
+------------------------------------+

Example 2 – Add a Second

In this example I add a second to the initial date.

SELECT TIMESTAMPADD(SECOND, 1, '1999-12-31');

Result:

+---------------------------------------+
| TIMESTAMPADD(SECOND, 1, '1999-12-31') |
+---------------------------------------+
| 1999-12-31 00:00:01                   |
+---------------------------------------+

The result is now a datetime value in order to return the seconds part.

Example 3 – Add a Microsecond

The previous example can be taken a step further and we can add a fractional seconds part. In this example I add a microsecond to the initial date.

SELECT TIMESTAMPADD(MICROSECOND, 1, '1999-12-31');

Result:

+--------------------------------------------+
| TIMESTAMPADD(MICROSECOND, 1, '1999-12-31') |
+--------------------------------------------+
| 1999-12-31 00:00:00.000001                 |
+--------------------------------------------+

Example 4 – Using a SQL_TSI_ Prefix

As mentioned, the unit can include a SQL_TSI_ prefix.

SELECT TIMESTAMPADD(SQL_TSI_YEAR, 1, '1999-12-31');

Result:

+---------------------------------------------+
| TIMESTAMPADD(SQL_TSI_YEAR, 1, '1999-12-31') |
+---------------------------------------------+
| 2000-12-31                                  |
+---------------------------------------------+

The TIMESTAMP() Function

You can use the TIMESTAMP() function to add multiple time parts to a date or datetime value at once.  So for example, you can add 12:35:26.123456 to a date if wish.

For more on this function, see TIMESTAMP() Examples.