{"id":2615,"date":"2013-05-21T18:24:20","date_gmt":"2013-05-22T02:24:20","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2615"},"modified":"2023-12-30T07:41:02","modified_gmt":"2023-12-30T14:41:02","slug":"mysql-now-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-now-function\/","title":{"rendered":"MySQL NOW() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL NOW()<\/code> function to get the current date and time of the server.<\/p>\n\n\n\n

Introduction to MySQL NOW() function<\/h2>\n\n\n\n

The MySQL NOW()<\/code> function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:DD'<\/code> or 'YYYYMMDDHHMMSS.uuuuuu'<\/code> format.<\/p>\n\n\n\n

Here’s the syntax of the NOW()<\/code> function:<\/p>\n\n\n

NOW()<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The returned type of the NOW()<\/code> function depends on the context where you use it. <\/p>\n\n\n\n
For example, in the following statement, the NOW()<\/code> function returns the current date and time as a string:<\/p>\n\n\n
SELECT<\/span> NOW<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try It Out<\/a><\/p>\n\n\n
+---------------------+\r\n| NOW()               |\r\n+---------------------+\r\n| 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:10<\/span>:54<\/span> |\r\n+---------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
However, in the numeric context, the NOW()<\/code> function returns the current date and time as a number as shown in the following example:<\/p>\n\n\n
SELECT<\/span> NOW<\/span>() + 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+----------------+\r\n| NOW() + 0<\/span>      |\r\n+----------------+\r\n| 20231230211120<\/span> |\r\n+----------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the NOW()<\/code> function returns the date and time at which the statement started executing. For example:<\/p>\n\n\n
SELECT<\/span> NOW<\/span>(), SLEEP<\/span>(5<\/span>), NOW<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------------+----------+---------------------+\r\n| NOW()               | SLEEP(5<\/span>) | NOW()               |\r\n+---------------------+----------+---------------------+\r\n| 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:11<\/span>:36<\/span> |        0<\/span> | 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:11<\/span>:36<\/span> |\r\n+---------------------+----------+---------------------+\r\n1<\/span> row in<\/span> set<\/span> (5.02 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In the query, the first NOW()<\/code> function executed, and the SLEEP(5)<\/code> function paused the execution of the query for 5 seconds, and the second NOW() function executed. <\/p>\n\n\n\n
However, both NOW()<\/code> functions return the same value, even when they are executed at different times.<\/p>\n\n\n\n
SELECT<\/span> SYSDATE<\/span>(), SLEEP<\/span>(5<\/span>), SYSDATE<\/span>();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------------+----------+---------------------+\r\n| SYSDATE()           | SLEEP(5<\/span>) | SYSDATE()           |\r\n+---------------------+----------+---------------------+\r\n| 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:11<\/span>:59<\/span> |        0<\/span> | 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:12<\/span>:04<\/span> |\r\n+---------------------+----------+---------------------+\r\n1<\/span> row in<\/span> set<\/span> (5.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to change the MySQL server’s time zone to adjust the current date and time returned by the NOW()<\/code> function, you use the following statement:<\/p>\n\n\n
SET<\/span> time_zone<\/span> = your_time_zone;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL NOW() function calculations<\/h2>\n\n\n\n
Because the NOW()<\/code> function returns a number when it is used in a numeric context, you can use it in calculations e.g., now plus 1 hour, now minus 1 hour, and now plus 1 day.<\/p>\n\n\n\n
The following statement returns the current date and time, now minus 1 hour and now plus 1 hour:<\/p>\n\n\n
-- mysql now minus 1 hour<\/span>\nSELECT<\/span> \n  (NOW<\/span>() - INTERVAL<\/span> 1<\/span> HOUR<\/span>) 'NOW - 1 hour'<\/span>, \n  NOW<\/span>(), \n-- mysql now plus 1 hour<\/span>\n  NOW<\/span>() + INTERVAL<\/span> 1<\/span> HOUR<\/span> 'NOW + 1 hour'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------------+---------------------+---------------------+\r\n| NOW - 1<\/span> hour        | NOW()               | NOW + 1<\/span> hour        |\r\n+---------------------+---------------------+---------------------+\r\n| 2023<\/span>-12<\/span>-30<\/span> 20<\/span>:12<\/span>:23<\/span> | 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:12<\/span>:23<\/span> | 2023<\/span>-12<\/span>-30<\/span> 22<\/span>:12<\/span>:23<\/span> |\r\n+---------------------+---------------------+---------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns the current date and time, now minus 1 day and now plus 1 day:<\/p>\n\n\n
SELECT<\/span> (NOW<\/span>() - INTERVAL<\/span> 1<\/span> DAY<\/span>) 'NOW - 1 day'<\/span>,\n        NOW<\/span>(),\n        (NOW<\/span>() + INTERVAL<\/span> 1<\/span> DAY<\/span>) 'NOW + 1 day'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------------+---------------------+---------------------+\r\n| NOW - 1<\/span> day         | NOW()               | NOW + 1<\/span> day         |\r\n+---------------------+---------------------+---------------------+\r\n| 2023<\/span>-12<\/span>-29<\/span> 21<\/span>:12<\/span>:37<\/span> | 2023<\/span>-12<\/span>-30<\/span> 21<\/span>:12<\/span>:37<\/span> | 2023<\/span>-12<\/span>-31<\/span> 21<\/span>:12<\/span>:37<\/span> |\r\n+---------------------+---------------------+---------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Using the NOW() function as the default value for a column<\/h2>\n\n\n\n
You can use the NOW()<\/code> function to provide a default value for a DATETIME<\/code> or TIMESTAMP<\/code> column. <\/p>\n\n\n\n
When you omit the date or time value in the INSERT<\/a><\/code> statement, MySQL automatically inserts the current date and time into the column whose default value is NOW()<\/code>. For example:<\/p>\n\n\n\n
First, create a new table<\/a> called contacts<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> contacts(\r\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\r\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\r\n    email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>,\r\n    created_at DATETIME NOT<\/span> NULL<\/span> DEFAULT<\/span> NOW<\/span>(),\r\n    updated_at DATETIME DEFAULT<\/span> NOW<\/span>() ON<\/span> UPDATE<\/span> NOW<\/span>()\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The contacts<\/code> table includes the following columns: <\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
If you want to get the exact time at which the statement executes, you use the SYSDATE()<\/code><\/a> function instead. For example:<\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n