Database Reference
In-Depth Information
the time at a certain point in that process. The SYSDATE() functionrecords the time at
which the function is executed, not the end of the statement. To see the difference, we can
introduce the SLEEP() function to tell MySQL to pause execution for a given number of
seconds. Here's a simple example showing the difference between NOW() and
SYSDATE() :
SELECT NOW(), SLEEP(4) AS 'Zzz', SYSDATE(), SLEEP(2) AS 'Zzz',
SYSDATE();
+---------------------+-----+---------------------+-----+---------------------+
| NOW() | Zzz | SYSDATE() | Zzz |
SYSDATE() |
+---------------------+-----+---------------------+-----+---------------------+
| 2014-02-21 05:44:57 | 0 | 2014-02-21 05:45:01 | 0 | 2014-02-21
05:45:03 |
+---------------------+-----+---------------------+-----+---------------------+
1 row in set (6.14 sec)
Notice that the difference between the time returned for NOW() and for the first
SYSDATE() is four seconds, the amount given with the first execution of SLEEP() . The
time between the two executions of SYSDATE() is two seconds, the amount given with
SLEEP() the second time. Notice also that the message after the results shows it took a
tad more than six seconds to execute this SQL statement. You probably won't use
SYSDATE() often — maybe never. It's useful primarily when you execute very complex
SQL statements or for more advanced usage (e.g., within stored procedures and triggers).
Let's move on to more common usage of functions related to the current date and time.
If the data type for a column is not DATETIME , you can still use the NOW() to get and
store the values you need. For instance, if the time_seen column had a datatype of
DATE and you entered the preceding INSERT statement, you'd get a warning saying data
truncated for column . However, it would still store the date correctly. A similar effect
would occuron a TIME column: you'd get a warning, but the time would be recorded cor-
rectly. It's better, though, to use the correct function. For DATE columns, use
CURDATE() . For TIME columns,use CURTIME() . The following example compares
these temporal functions:
SELECT NOW( ), CURDATE( ), CURTIME( );
+---------------------+------------+--------------+
| NOW( ) | CURDATE( ) | CURTIME( ) |
+---------------------+------------+--------------+
Search WWH ::




Custom Search