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