NOW( )

This MySQL function returns the current date and time. The format returned is yyyy-mm-dd hh:mm:ss.nnnnnn. It's synonymous with LOCALTIME( ) and LOCALTIMESTAMP( ).

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

NOW( )

Explanation

This function returns the current date and time. The format returned is yyyy-mm-dd hh:mm:ss.nnnnnn, unless the function is used in a numeric calculation. Then it will return the data in a yyyymmdd format. It's synonymous with LOCALTIME() and LOCALTIMESTAMP().

Examples

SELECT NOW( ) AS Now,
NOW( ) + 105008 AS '1 hour, 50 min., 8 sec. Later';

+---------------------+-------------------------------+
| Now                 | 1 hour, 50 min., 8 sec. Later |
+---------------------+-------------------------------+
| 2007-03-18 20:08:30 |         20070318305838.000000 | 
+---------------------+-------------------------------+

By adding 105008 to the current time, the hour is increased by 1, the minutes by 50, and the seconds by 8, and the time is displayed in the second field without dashes. Notice that the results show the hours to be 30 now and not 6, and the date wasn't adjusted. Raw adding of time is usually not a good alternative to functions such as DATE_ADD() or TIME_ADD().

The NOW() function is similar to the SYSDATE() function in that they both return the current datetime 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. This can lead to differences when long triggers or stored procedures run; an embedded SYSDATE() will then reflect a later time than NOW(). For this reason, there are potential problems using SYSDATE() with regard to replication. See the description of SYSDATE() for more information.

SELECT NOW( ) AS 'Start',
SLEEP(5) AS 'Pause',
NOW( ) AS 'Middle But Same',
SYSDATE( ) AS 'End';

+---------------------+-------+---------------------+---------------------+
| Start               | Pause | Middle But Same     | End                 |
+---------------------+-------+---------------------+---------------------+
| 2008-06-15 11:02:41 |     0 | 2008-06-15 11:02:41 | 2008-06-15 11:02:46 | 
+---------------------+-------+---------------------+---------------------+
1 row in set (5.27 sec)

MySQL executes the elements of a SELECT statement from left to right, so the Start field was determined first. The SLEEP() function instructs the server to pause the execution of the SQL statement by the amount of seconds given. After this, the third element is executed. As you can see, the results for that third field are the same as the first because NOW() returns the starting time. However, in the fourth field, SYSDATE() returns the time it was executed, five seconds after the start. This may not seem like much of a difference between the functions, but there may be situations where it matters. In particular, it may matter with SQL statements, triggers, or stored procedures that take a long time to finish executing.