MySQL SYSDATE Function
Summary: in this tutorial, you will learn about the MySQL SYSDATE() function and its caveat.
Introduction to MySQL SYSDATE function
The following illustrates the syntax of the SYSDATE() function:
SYSDATE(fsp);
Code language: SQL (Structured Query Language) (sql)
The SYSDATE() function returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' format if the function is used in a string context or YYYYMMDDHHMMSS format in case the function is used in a numeric context.
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.
mysql> SELECT SYSDATE();
+---------------------+
| 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:
mysql> SELECT SYSDATE(3);
+-------------------------+
| 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
Consider the following example.
mysql> SELECT SYSDATE(),
NOW();
+---------------------+---------------------+
| 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 a 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.
See the following query:
mysql> SELECT NOW(),
SLEEP(5),
NOW();
+---------------------+----------+---------------------+
| 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 used 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 began.
Let’s change the NOW() function to SYSDATE() function:
mysql> SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| 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 shown in the result the EXPLAIN below:
EXPLAIN SELECT
id,
t
FROM
tests
WHERE
t >= NOW() - INTERVAL 1 DAY;
Code language: SQL (Structured Query Language) (sql)
--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 reason why you should consider twice before using it.