How to Use TIMESTAMPADD Function in MySQL

Use the TIMESTAMPADD function in MySQL to add a specified interval to a date or datetime value. The syntax is TIMESTAMPADD(unit, interval, datetime), where unit is the type of interval (like SECOND, MINUTE, DAY), interval is the number to add, and datetime is the starting date or time.

Syntax

The TIMESTAMPADD function adds a time interval to a date or datetime value. It takes three parts:

• unit: The type of interval to add (e.g., SECOND, MINUTE, HOUR, DAY, MONTH, YEAR).
• interval: The number of units to add (can be positive or negative).
• datetime: The starting date or datetime value.

The function returns a new datetime value with the interval added.

sql

TIMESTAMPADD(unit, interval, datetime)

Example

This example adds 5 days to the date '2024-06-01'. It shows how to use TIMESTAMPADD to get a new date 5 days later.

sql

SELECT TIMESTAMPADD(DAY, 5, '2024-06-01') AS new_date;

Output

2024-06-06

Common Pitfalls

Common mistakes include:

• Using the wrong unit name (units must be valid MySQL interval units like SECOND, MINUTE, HOUR, DAY, MONTH, YEAR).
• Passing a string that is not a valid date or datetime as the third argument.
• Confusing TIMESTAMPADD with DATE_ADD (both add intervals but have different syntax).

Always ensure the interval is a number and the datetime is a valid date or datetime value.

sql

/* Wrong: invalid unit name */
SELECT TIMESTAMPADD(DAYS, 5, '2024-06-01');

/* Right: correct unit name */
SELECT TIMESTAMPADD(DAY, 5, '2024-06-01');

Quick Reference

Unit	Description
SECOND	Add seconds
MINUTE	Add minutes
HOUR	Add hours
DAY	Add days
WEEK	Add weeks
MONTH	Add months
QUARTER	Add quarters (3 months)
YEAR	Add years

Key Takeaways

TIMESTAMPADD adds a specified interval to a date or datetime value in MySQL.

Use valid interval units like SECOND, MINUTE, DAY, MONTH, or YEAR.

The interval must be a number and datetime must be a valid date or datetime.

TIMESTAMPADD syntax is TIMESTAMPADD(unit, interval, datetime).

Common errors come from invalid units or invalid date formats.