Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the system date at the time it is executed. It will return the date and time in the yyyy-mm-dd hh:mm:ss format, but will return the data in the yyyymmddhhmmss format if it’s used as part of a numeric calculation. It will display the microseconds value if the calculation involves a microseconds value. Here is an example:

SELECT SYSDATE( ), 
SYSDATE( ) + 0 AS 'Numeric Format';

+---------------------+----------------+
| SYSDATE( )          | Numeric Format |
+---------------------+----------------+
| 2008-03-15 23:37:38 | 20080315233738 | 
+---------------------+----------------+

This function is similar to the NOW() function in that they both return the current datetime and in the same format. However, the NOW() function returns the time when the SQL statement began, whereas SYSDATE() returns the time the function was invoked. See the definition of NOW() earlier in this chapter for an example of this situation and its significance.

If you’re using replication, the binary log will include SET TIMESTAMP entries, so if you restore a database from the binary log, values from NOW() will be adjusted to the same times as when the original SQL statements were executed. SYSDATE() entries are unaffected by these SET TIMESTAMP entries:

SET @yesterday = UNIX_TIMESTAMP(SUBDATE(SYSDATE( ), 1));

SELECT FROM_UNIXTIME(@yesterday);

+---------------------------+
| FROM_UNIXTIME(@yesterday) |
+---------------------------+
| 2008-03-17 00:19:17       | 
+---------------------------+

SET TIMESTAMP = @yesterday;

SELECT NOW( ), SYSDATE( );

+---------------------+---------------------+
| NOW( )              | SYSDATE( )          |
+---------------------+---------------------+
| 2008-03-17 00:19:17 | 2008-03-16 00:22:53 | 
+---------------------+---------------------+

These statements are more involved than necessary, but they help illustrate my point. In the first SQL statement, we use the SET statement to set up a user variable to hold the date and time of yesterday. To change the TIMESTAMP variable, we need the new datetime in the Unix time format, so we use UNIX_TIMESTAMP(). Within that function, we use SUBDATE() to get the datetime for one day before. The second statement is just so we can see the value of the user variable. With the third statement, we set the system variable to the value of the user variable we created. The result is that when we run the last SQL statement—the SELECT() with both NOW() and SYSDATE()—we can see that the results are different by the one day and also a few seconds. The difference is that the value for NOW() is locked because we set the TIMESTAMP variable.

If you’re replicating, you may not want to use SYSDATE() for setting values, as their results won’t be replicated if you restore the data later. It is possible to resolve this problem by starting the server with the --sysdate-is-now option. This will cause SYSDATE() to function the same as NOW().