When returning the current date/time in MySQL, there are a range of functions you can choose from. Most of these are simply synonyms for another function.

However, there are two functions that appear to do the same thing, but are in actual fact, slightly different. The functions I’m referring to are SYSDATE() and NOW().

The Difference Between SYSDATE() and NOW()

Both functions return the current date and time. However, here’s where they differ:

• SYSDATE() returns the time at which it executes.
• NOW() returns a constant time that indicates the time at which the statement began to execute.
• The SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE().

So in many cases, you’ll probably get the same result regardless of which function you use. However, if your statement is more complex and takes a while to run, you could end up with quite a large difference in the return value depending on which function you use.

Example

Here’s an example to demonstrate how the return value can vary, depending on which function you use.

SELECT
    SYSDATE(), SLEEP(10) AS '', SYSDATE(),
    NOW(), SLEEP(10) AS '', NOW();

Result:

+---------------------+---+---------------------+---------------------+---+---------------------+
| SYSDATE()           |   | SYSDATE()           | NOW()               |   | NOW()               |
+---------------------+---+---------------------+---------------------+---+---------------------+
| 2018-06-23 11:55:26 | 0 | 2018-06-23 11:55:36 | 2018-06-23 11:55:26 | 0 | 2018-06-23 11:55:26 |
+---------------------+---+---------------------+---------------------+---+---------------------+

So in this example, I run SYSDATE() twice, but with a 10 second pause in between. I then do the same thing with NOW().

As expected, the 10 second pause affects the return value of the second SYSDATE() but not the second NOW(). In fact, the values for both instances of NOW() are the same as for the first instance of SYSDATE(), which is when the statement started running.

Making SYSDATE() an Alias for NOW()

If the difference between these two functions is causing you problems, you actually have the option to make SYSDATE() an alias for NOW().

To do this, use the --sysdate-is-now server command option. For more information, see –sysdate-is-now on the MySQL documentation website.