{"id":2856,"date":"2018-07-02T17:37:02","date_gmt":"2018-07-03T00:37:02","guid":{"rendered":"https:\/\/database.guide\/?p=2856"},"modified":"2021-01-28T14:10:36","modified_gmt":"2021-01-28T04:10:36","slug":"timestampadd-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/timestampadd-examples-mysql\/","title":{"rendered":"TIMESTAMPADD() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, the TIMESTAMPADD()<\/code> 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.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

TIMESTAMPADD(unit,interval,datetime_expr)\r\n<\/pre>\n
Where unit<\/code> is the unit to add, interval<\/code> is how many of the units to add, and datetime_expr<\/code> is the initial date or datetime value.<\/p>\n
The unit argument can be\u00a0either MICROSECOND<\/code>\u00a0SECOND<\/code>,\u00a0MINUTE<\/code>,\u00a0HOUR<\/code>,\u00a0DAY<\/code>,\u00a0WEEK<\/code>,\u00a0MONTH<\/code>,\u00a0QUARTER<\/code>, or YEAR<\/code>.<\/p>\n
The unit argument can also have a prefix of SQL_TSI_<\/code>. For example, you could use either DAY<\/code> or SQL_TSI_DAY<\/code>.<\/p>\n
Example 1 – Add a Day<\/h2>\n
In this example I add a day to the initial date.<\/p>\n
SELECT TIMESTAMPADD(DAY, 1, '1999-12-31');\r\n<\/pre>\n
Result:<\/p>\n
+------------------------------------+\r\n| TIMESTAMPADD(DAY, 1, '1999-12-31') |\r\n+------------------------------------+\r\n| 2000-01-01                         |\r\n+------------------------------------+\r\n<\/pre>\n
Example 2 – Add a Second<\/h2>\n
In this example I add a second to the initial date.<\/p>\n
SELECT TIMESTAMPADD(SECOND, 1, '1999-12-31');\r\n<\/pre>\n
Result:<\/p>\n
+---------------------------------------+\r\n| TIMESTAMPADD(SECOND, 1, '1999-12-31') |\r\n+---------------------------------------+\r\n| 1999-12-31 00:00:01                   |\r\n+---------------------------------------+\r\n<\/pre>\n
The result is now a datetime value in order to return the seconds part.<\/p>\n
Example 3 – Add a Microsecond<\/h2>\n
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.<\/p>\n
SELECT TIMESTAMPADD(MICROSECOND, 1, '1999-12-31');\r\n<\/pre>\n
Result:<\/p>\n
+--------------------------------------------+\r\n| TIMESTAMPADD(MICROSECOND, 1, '1999-12-31') |\r\n+--------------------------------------------+\r\n| 1999-12-31 00:00:00.000001                 |\r\n+--------------------------------------------+\r\n<\/pre>\n
Example 4 – Using a SQL_TSI_ Prefix<\/h2>\n
As mentioned, the unit can include a SQL_TSI_<\/code> prefix.<\/p>\n
SELECT TIMESTAMPADD(SQL_TSI_YEAR, 1, '1999-12-31');\r\n<\/pre>\n
Result:<\/p>\n
+---------------------------------------------+\r\n| TIMESTAMPADD(SQL_TSI_YEAR, 1, '1999-12-31') |\r\n+---------------------------------------------+\r\n| 2000-12-31                                  |\r\n+---------------------------------------------+\r\n<\/pre>\n
The TIMESTAMP() Function<\/h2>\n
You can use the TIMESTAMP()<\/code> function to add multiple time parts to a date or datetime value at once.\u00a0 So for example, you can add 12:35:26.123456 to a date if wish.<\/p>\n
For more on this function, see TIMESTAMP()<\/code> Examples<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2856","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2856","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2856"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2856\/revisions"}],"predecessor-version":[{"id":2860,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2856\/revisions\/2860"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2856"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2856"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2856"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}