MySQL in a Nutshell, 2nd Edition

Name

NOW()

Synopsis

NOW()

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(). Here is an example:

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 105,008 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 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() later in this chapter 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 is 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.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition