{"id":2849,"date":"2018-07-02T16:39:04","date_gmt":"2018-07-02T23:39:04","guid":{"rendered":"https:\/\/database.guide\/?p=2849"},"modified":"2021-01-28T14:10:36","modified_gmt":"2021-01-28T04:10:36","slug":"timestamp-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/timestamp-examples-mysql\/","title":{"rendered":"TIMESTAMP() Examples – MySQL"},"content":{"rendered":"

In MySQL<\/a>, the TIMESTAMP()<\/code> function returns a datetime value based on the argument\/s passed in. You can provide one argument or two. If you provide two, it adds the second one to the first and returns the result.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

You can use either of these two forms:<\/p>\n

TIMESTAMP(expr)\r\nTIMESTAMP(expr1,expr2)\r\n<\/pre>\n
The first argument (expr<\/code> and expr1<\/code>) is a date or datetime expression. The second argument (expr2<\/code>) is a time expression. If you provide two arguments, expr2<\/code> is added to expr1<\/code> and the result is a datetime value.<\/p>\n
Example 1 – Provide a ‘date’ Argument<\/h2>\n
In this example I provide a date expression.<\/p>\n
SELECT TIMESTAMP('1999-12-31');\r\n<\/pre>\n
Result:<\/p>\n
+-------------------------+\r\n| TIMESTAMP('1999-12-31') |\r\n+-------------------------+\r\n| 1999-12-31 00:00:00     |\r\n+-------------------------+\r\n<\/pre>\n
So the result is a datetime value regardless of whether our argument was a date or datetime expression.<\/p>\n
Example 2 – Provide a ‘datetime’ Argument<\/h2>\n
And here’s an example using a datetime expression.<\/p>\n
SELECT TIMESTAMP('1999-12-31 23:59:59');\r\n<\/pre>\n
Result:<\/p>\n
+----------------------------------+\r\n| TIMESTAMP('1999-12-31 23:59:59') |\r\n+----------------------------------+\r\n| 1999-12-31 23:59:59              |\r\n+----------------------------------+\r\n<\/pre>\n
Example 3 – Include Fractional Seconds<\/h2>\n
You can also include a fractional seconds part up to microseconds (6 digits). When you do this, the result will also include that part.<\/p>\n
SELECT TIMESTAMP('1999-12-31 23:59:59.999999');\r\n<\/pre>\n
Result:<\/p>\n
+-----------------------------------------+\r\n| TIMESTAMP('1999-12-31 23:59:59.999999') |\r\n+-----------------------------------------+\r\n| 1999-12-31 23:59:59.999999              |\r\n+-----------------------------------------+\r\n<\/pre>\n
Example 4 – Providing 2 Arguments<\/h2>\n
Here’s an example using two arguments. As mentioned, the second one gets added to the first.<\/p>\n
SELECT TIMESTAMP('1999-12-31', '12:30:45');\r\n<\/pre>\n
Result:<\/p>\n
+-------------------------------------+\r\n| TIMESTAMP('1999-12-31', '12:30:45') |\r\n+-------------------------------------+\r\n| 1999-12-31 12:30:45                 |\r\n+-------------------------------------+\r\n<\/pre>\n
Example 5 – Larger ‘time’ Values<\/h2>\n
The time data type can have a range from -838:59:59<\/em> to 838:59:59<\/em>. This is because it’s not just limited to representing the time of day. It could also be used to represent elapsed time. In this case, we use it to add a large number of hours to a date value.<\/p>\n
SELECT TIMESTAMP('1999-12-31', '400:30:45');\r\n<\/pre>\n
Result:<\/p>\n
+--------------------------------------+\r\n| TIMESTAMP('1999-12-31', '400:30:45') |\r\n+--------------------------------------+\r\n| 2000-01-16 16:30:45                  |\r\n+--------------------------------------+\r\n<\/pre>\n
Example 6 – Negative Values<\/h2>\n
You can subtract the second argument from the first, simply by prefixing the second value with a minus sign.<\/p>\n
SELECT TIMESTAMP('1999-12-31', '-400:30:45');\r\n<\/pre>\n
Result:<\/p>\n
+---------------------------------------+\r\n| TIMESTAMP('1999-12-31', '-400:30:45') |\r\n+---------------------------------------+\r\n| 1999-12-14 07:29:15                   |\r\n+---------------------------------------+\r\n<\/pre>\n
Example 7 – Using the Current Date<\/h2>\n
If you want a timestamp that uses the current date, you might be more interested in functions such as NOW()<\/code><\/a>, CURDATE()<\/code><\/a>, and possibly even SYSDATE()<\/code><\/a> (for the difference, see SYSDATE()<\/code> vs NOW()<\/code><\/a>).<\/p>\n
However, the TIMESTAMP()<\/code> function may still be your preferred function in certain cases, for example if you want to add a time value to the current date.<\/p>\n
SELECT \r\n    TIMESTAMP(CURDATE()) AS 'Today',\r\n    TIMESTAMP(CURDATE(), '24:00:00') AS 'Tomorrow';\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+---------------------+\r\n| Today               | Tomorrow            |\r\n+---------------------+---------------------+\r\n| 2018-07-03 00:00:00 | 2018-07-04 00:00:00 |\r\n+---------------------+---------------------+\r\n<\/pre>\n
The TIMESTAMPADD() Function<\/h2>\n
You can use the TIMESTAMPADD()<\/code> function to add a specified unit to a date or datetime value. This function also accepts units such as days, months, years etc.<\/p>\n
If you find TIMESTAMP()<\/code> too restrictive for your needs, see TIMESTAMPADD()<\/code><\/a> Examples.<\/p>\n","protected":false},"excerpt":{"rendered":"
In MySQL, the TIMESTAMP() function returns a datetime value based on the argument\/s passed in. You can provide one argument or two. If you provide two, it adds the second one to the first and returns the result.<\/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-2849","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\/2849","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=2849"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2849\/revisions"}],"predecessor-version":[{"id":2861,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2849\/revisions\/2861"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2849"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2849"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2849"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}