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( ) |
+---------------------+------------+--------------+