MySQL in a Nutshell, 2nd Edition

Name

SYSDATE()

Synopsis

SYSDATE()

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 | +---------------------------+ ...