How to use the MySQL TIMESTAMPADD() function

In this article, we will learn how to use the MySQL TIMESTAMPADD() function, which returns the timestamp value that is the result of adding a specified interval to a timestamp value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with timestamps and intervals.

Syntax

The syntax of the TIMESTAMPADD() function is as follows:

TIMESTAMPADD(unit, interval, timestamp)

The unit parameter can be any of the date and time units that are supported by MySQL, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, etc. The interval parameter can be any valid integer expression that specifies the number of units to be added to the timestamp value. The timestamp parameter can be any valid timestamp expression, or a string that can be converted to a timestamp value. If any parameter is NULL, the function returns NULL. The TIMESTAMPADD() function returns a timestamp value that is the result of adding the specified interval to the given timestamp value. The format of the timestamp value is ‘YYYY-MM-DD HH:MM:SS’. For example, TIMESTAMPADD(MINUTE, 1, '2023-01-15 10:02:34') returns ‘2023-01-15 10:03:34’, which is the timestamp value after adding one minute.

Examples

Let’s see some examples of how to use the TIMESTAMPADD() function in MySQL.

Example 1: Add a second to a timestamp value

We can use the TIMESTAMPADD() function to add a second to a timestamp value. For example:

SELECT TIMESTAMPADD(SECOND, 1, '2023-01-15 10:02:34') AS result;

This query will add one second to the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 10:02:35’, which is the timestamp value after adding one second.

Example 2: Add a minute to a timestamp value

We can use the TIMESTAMPADD() function to add a minute to a timestamp value. For example:

SELECT TIMESTAMPADD(MINUTE, 1, '2023-01-15 10:02:34') AS result;

This query will add one minute to the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 10:03:34’, which is the timestamp value after adding one minute.

Example 3: Add an hour to a timestamp value

We can use the TIMESTAMPADD() function to add an hour to a timestamp value. For example:

SELECT TIMESTAMPADD(HOUR, 1, '2023-01-15 10:02:34') AS result;

This query will add one hour to the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-15 11:02:34’, which is the timestamp value after adding one hour.

Example 4: Add a day to a timestamp value

We can use the TIMESTAMPADD() function to add a day to a timestamp value. For example:

SELECT TIMESTAMPADD(DAY, 1, '2023-01-15 10:02:34') AS result;

This query will add one day to the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-01-16 10:02:34’, which is the timestamp value after adding one day.

Example 5: Add a month to a timestamp value

We can use the TIMESTAMPADD() function to add a month to a timestamp value. For example:

SELECT TIMESTAMPADD(MONTH, 1, '2023-01-15 10:02:34') AS result;

This query will add one month to the timestamp value ‘2023-01-15 10:02:34’. The query will return ‘2023-02-15 10:02:34’, which is the timestamp value after adding one month.

There are some other functions that are related to the TIMESTAMPADD() function, and can be useful for working with timestamps and intervals. Here are some of them:

TIME(): This function returns the time part of a date or datetime value, or a string that can be converted to a time value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
DATE(): This function returns the date part of a date or datetime value, or a string that can be converted to a date value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
DATETIME(): This function returns the datetime value of a date or datetime value, or a string that can be converted to a datetime value. For example, DATETIME('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
TIMESTAMP(): This function returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. For example, TIMESTAMP('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
TIMESTAMPDIFF(): This function returns the difference between two timestamp values, in a specified unit. For example, TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') returns 60, which is the number of seconds between the two timestamp values.

Conclusion

In this article, we learned how to use the MySQL TIMESTAMPADD() function, which returns the timestamp value that is the result of adding a specified interval to a timestamp value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with timestamps and intervals.