MySQL SYSDATE Function

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.