MySQL | TIMESTAMPADD method
Start your free 7-days trial now!
MySQL's TIMESTAMP_ADD(~) method adds the specified unit interval to a date or datetime value.
Parameters
1. unit | unit
The unit for the interval specified.
Possible unit values |
|---|
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
2. interval | integer
The interval to add to the provided date/datetime.
3. datetime_expr | date/datetime
The date/datetime value to add the interval to.
Return value
The result of adding the specified unit interval to the input date or datetime.
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To add 1 hour to student enrollment dates:
SELECT fname, day_enrolled, TIMESTAMPADD(HOUR, 1, day_enrolled)FROM students;
+----------+--------------+-------------------------------------+| fname | day_enrolled | TIMESTAMPADD(HOUR, 1, day_enrolled) |+----------+--------------+-------------------------------------+| Sky | 2015-12-03 | 2015-12-03 01:00:00 || Ben | 2016-04-20 | 2016-04-20 01:00:00 || Travis | 2018-08-14 | 2018-08-14 01:00:00 || Arthur | 2016-04-01 | 2016-04-01 01:00:00 || Benjamin | 2014-01-01 | 2014-01-01 01:00:00 |+----------+--------------+-------------------------------------+
To add 2 months to student enrollment dates:
SELECT fname, day_enrolled, TIMESTAMPADD(MONTH, 2, day_enrolled)FROM students;
+----------+--------------+--------------------------------------+| fname | day_enrolled | TIMESTAMPADD(MONTH, 2, day_enrolled) |+----------+--------------+--------------------------------------+| Sky | 2015-12-03 | 2016-02-03 || Ben | 2016-04-20 | 2016-06-20 || Travis | 2018-08-14 | 2018-10-14 || Arthur | 2016-04-01 | 2016-06-01 || Benjamin | 2014-01-01 | 2014-03-01 |+----------+--------------+--------------------------------------+
Negative intervals
To add negative intervals (i.e. subtract intervals):
SELECT fname, day_enrolled, TIMESTAMPADD(DAY, -1, day_enrolled)FROM students;
+----------+--------------+-------------------------------------+| fname | day_enrolled | TIMESTAMPADD(DAY, -1, day_enrolled) |+----------+--------------+-------------------------------------+| Sky | 2015-12-03 | 2015-12-02 || Ben | 2016-04-20 | 2016-04-19 || Travis | 2018-08-14 | 2018-08-13 || Arthur | 2016-04-01 | 2016-03-31 || Benjamin | 2014-01-01 | 2013-12-31 |+----------+--------------+-------------------------------------+
Here we subtracted 1 day from student enrollment dates.
