{"id":6285,"date":"2017-07-13T05:06:05","date_gmt":"2017-07-13T12:06:05","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6285"},"modified":"2023-10-18T03:15:15","modified_gmt":"2023-10-18T10:15:15","slug":"mysql-sysdate","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-sysdate\/","title":{"rendered":"MySQL SYSDATE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL SYSDATE()<\/code> function and its caveat.<\/p>\n\n\n\n

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

The SYSDATE()<\/code> function returns the current date<\/a> and time<\/a> at which it is executed. If you use the function in the string context, the return value in the 'YYYY-MM-DD HH:MM:SS'<\/code> format. However, if you use the function in a numeric context, it returns a value in the YYYYMMDDHHMMSS<\/code> format.<\/p>\n\n\n\n

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

SYSDATE(fsp);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The SYSDATE()<\/code> function accepts an optional argument fsp<\/code> that determines whether the result should include a fractional seconds precision which ranges from 0 to 6.<\/p>\n\n\n\n
See the following example:<\/p>\n\n\n
SELECT SYSDATE();<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+<\/span>\n| SYSDATE()           |\n+---------------------+<\/span>\n| 2017-07-13 17:42:37 |\n+---------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you pass the fsp<\/code> argument, the result will include the fractional seconds precision as shown in the following example:<\/p>\n\n\n
SELECT SYSDATE(3);<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------------+<\/span>\n| SYSDATE(3)              |\n+-------------------------+<\/span>\n| 2017-07-13 17:42:55.875 |\n+-------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SYSDATE vs. NOW<\/h2>\n\n\n\n
The following example uses the SYSDATE()<\/code> and NOW()<\/code> functions in the same query:<\/p>\n\n\n
SELECT \n  SYSDATE(), \n  NOW();<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+---------------------+<\/span>\n| SYSDATE()           | NOW()               |\n+---------------------+---------------------+<\/span>\n| 2017-07-13 17:46:30 | 2017-07-13 17:46:30 |\n+---------------------+---------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the SYSDATE()<\/code> function actually returns the time at which it executes while the NOW()<\/code> function returns a constant time at which the statement began to execute. For example:<\/p>\n\n\n
SELECT \n  NOW(), \n  SLEEP(5), \n  NOW();<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+----------+---------------------+<\/span>\n| NOW()               | SLEEP(5) | NOW()               |\n+---------------------+----------+---------------------+<\/span>\n| 2017-07-13 17:49:18 |        0 | 2017-07-13 17:49:18 |\n+---------------------+----------+---------------------+<\/span>\n1 row in set<\/span> (5.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we use the SLEEP()<\/code> function to pause the query for 5 seconds. Within the same statement, the NOW()<\/code> function always returns a constant which is the time at which the statement starts.<\/p>\n\n\n\n
Let’s change the NOW()<\/code> function to SYSDATE()<\/code> function:<\/p>\n\n\n
SELECT \n  SYSDATE(), \n  SLEEP(5), \n  SYSDATE();<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------------------+----------+---------------------+<\/span>\n| SYSDATE()           | SLEEP(5) | SYSDATE()           |\n+---------------------+----------+---------------------+<\/span>\n| 2017-07-13 17:50:57 |        0 | 2017-07-13 17:51:02 |\n+---------------------+----------+---------------------+<\/span>\n1 row in set<\/span> (5.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Within the same statement, SYSDATE()<\/code> function returns different time values that reflect the time at which the SYSDATE()<\/code> function was executed.<\/p>\n\n\n\n
To demonstrate this, we will create a table<\/a> named tests<\/code> and insert some data<\/a> into this table.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tests (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    t DATETIME UNIQUE<\/span>\n);\n\nINSERT<\/span> INTO<\/span> tests(t) \nWITH<\/span> RECURSIVE<\/span> times(t) AS<\/span>\n( \n    SELECT<\/span> now<\/span>() - interval<\/span> 1<\/span> YEAR<\/span> t\n        UNION<\/span> ALL<\/span> \n    SELECT<\/span> t + interval<\/span> 1<\/span> hour<\/span>\n    FROM<\/span> times\n    WHERE<\/span> t < now<\/span>() \n)\nSELECT<\/span> t\nFROM<\/span> times;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the t<\/code> column has a unique index<\/a>, the following query should execute fast:<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>, \n    t\nFROM<\/span>\n    tests\nWHERE<\/span>\n    t >= SYSDATE<\/span>() - INTERVAL<\/span> 1<\/span> DAY<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, it took 15ms to complete. Let’s see the detail using the EXPLAIN<\/code> statement.<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    id<\/span>, t\nFROM<\/span>\n    tests\nWHERE<\/span>\n    t >= SYSDATE<\/span>() - INTERVAL<\/span> 1<\/span> DAY<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
It turned out that MySQL had to scan all the rows in the table to get the data. The index could not utilized.<\/p>\n\n\n\n
If you change the SYSDATE()<\/code> to NOW()<\/code> function in the query:<\/p>\n\n\n
SELECT<\/span> \n    id<\/span>, \n    t\nFROM<\/span>\n    tests\nWHERE<\/span>\n    t >= NOW<\/span>() - INTERVAL<\/span> 1<\/span> DAY<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
With the NOW()<\/code> function, the index has been used for querying data as demonstrated in the result of the EXPLAIN<\/code> statement below:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    id<\/span>, \n    t\nFROM<\/span>\n    tests\nWHERE<\/span>\n    t >= NOW<\/span>() - INTERVAL<\/span> 1<\/span> DAY<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Note that MySQL provides you with the --sysdate-is-now<\/code> option that can make the SYSDATE()<\/code> function behaves the same as the NOW()<\/code> function.<\/p>\n\n\n\n
In this tutorial, you have learned about the MySQL SYSDATE()<\/code> function and the reasons why you should consider using it.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Notice that we used a recursive CTE<\/a> for generating time series. The CTE has been available since MySQL 8.0<\/p>\n\n\n\n
Because the SYSDATE()<\/code> function is non-deterministic, indexes<\/a> cannot be utilized for evaluating expressions that refer to it.<\/p>\n\n\n\n
It seems that both SYSDATE()<\/code> and NOW()<\/a><\/code> functions return the same value which is the current date and time at which it is executed.<\/p>\n\n\n\n