MySQL SYSDATE Function

 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);

The SYSDATE() the 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 the format in case the function is used in a numeric context.

The SYSDATE() the function accepts an optional argument fsp that determines whether the result should include a fractional second 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)

If you pass the fsp the 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)

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)

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() the function actually returns the time at which it executes while the NOW() the 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)

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)

Within the same statement, SYSDATE() the function returns different time values that reflect the time at which the SYSDATE() the function was executed.

Because the SYSDATE() the 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;

Notice that we used a recursive CTE for generating time series. The CTE has been available since MySQL 8.0

Because the t the column has a unique index, the following query should execute fast:

SELECT 
    id, 
    t
FROM
    tests
WHERE
    t >= SYSDATE() - INTERVAL 1 DAY;

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;

It turned out that MySQL had to scan all the rows in the table to get the data. The index could not be utilized.

If you change the SYSDATE() to NOW() function in the query:

SELECT 
    id, 
    t
FROM
    tests
WHERE
    t >= NOW() - INTERVAL 1 DAY;

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;

Note that MySQL provides you with the --sysdate-is-now an option that can make the SYSDATE() the 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.