SYSDATE( )

This MySQL function returns the system date in the yyyy-mm-dd hh:mm:ss format at the time it's executed.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SYSDATE( )

Explanation

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.

Examples

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( ) for an example of this situation and its significance.

If you're using replication, the binary log will include SET TIMESTAMP entries, so that 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 to 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're using SUBDATE( ) to get the datetime 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 —but 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( ).