5.4 Determining the Current Date or Time
5.4.1 Problem
What's the date? What time is it?
5.4.2 Solution
Use the NOW(
),
CURDATE( ), or CURTIME(
)
functions.
5.4.3 Discussion
Some applications need to know the current date or time, such as
those that produce a datestamped or timestamped status display. This
kind of information is also useful for date calculations that are
performed in relation to the current date, such as finding the first
(or last) day of the month, or determining the date for Wednesday of
next week.
The current date and time are available through three functions.
NOW( ) returns both the current date and time.
CURDATE( ) and CURTIME( )
return the date and time separately:
mysql> SELECT NOW( ), CURDATE( ), CURTIME( );
+---------------------+------------+-----------+
| NOW( ) | CURDATE( ) | CURTIME( ) |
+---------------------+------------+-----------+
| 2002-07-15 10:59:30 | 2002-07-15 | 10:59:30 |
+---------------------+------------+-----------+
CURRENT_TIMESTAMP and SYSDATE(
) are synonyms for NOW( ).
CURRENT_DATE and CURRENT_TIME
are synonyms for CURDATE( ) and CURTIME(
).
If you want to obtain subparts of these values (such as the current
day of the month or current hour of the day), read the next few
sections.
|
Functions such as
NOW( ) and CURDATE(
) are
commonly (but mistakenly) used in CREATE
TABLE statements as default values:
mysql> CREATE TABLE testtbl (dt DATETIME DEFAULT NOW( ));
You have an error in your SQL syntax near 'NOW( ))' at line 1
The intent here is that values of the dt column
should be initialized automatically to the date and time at which
records are created. But it won't work; default
values in MySQL must be constants. If you want a column set to the
current date and time at record creation, use a
TIMESTAMP, which MySQL will initialize
automatically, or use a
DATETIME and set the initial value yourself
when you create records.
The restriction on non-constant default values will be lifted in the
future, during the development of MySQL 4.1.
|
|
