DuBois P. MySQL Cookbook / 5.4 Determining the Current Date or Time

Computers & Technology
Education & Reference
Business & Investing
Science & Math

MySQL Cookbook
Authors: DuBois P.
Published year: 2005
Pages: 110-111/412

Buy this book on amazon.com >>

<< Previous page
Table of contents
Next page >>

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.

NOW( ) Is Not a Valid Column Default Value

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.

5.5 Decomposing Dates and Times Using Formatting Functions

5.5.1 Problem

You want to obtain just a part of a date or a time.

5.5.2 Solution

Use a formatting function such as DATE_FORMAT( ) or TIME_FORMAT( ) with a format string that includes a specifier for the part of the value you want to obtain.

5.5.3 Discussion

MySQL provides several options for decomposing dates or times to obtain their component values. The DATE_FORMAT( ) and TIME_FORMAT( ) functions provide one way to extract individual parts of temporal values:

mysql>

SELECT dt,

->

DATE_FORMAT(dt,'%Y') AS year,

->

DATE_FORMAT(dt,'%d') AS day,

->

TIME_FORMAT(dt,'%H') AS hour,

->

TIME_FORMAT(dt,'%s') AS second

->

FROM datetime_val;

+---------------------+------+------+------+--------+
 dt                   year  day   hour  second 
+---------------------+------+------+------+--------+
 1970-01-01 00:00:00  1970  01    00    00     
 1987-03-05 12:30:15  1987  05    12    15     
 1999-12-31 09:00:00  1999  31    09    00     
 2000-06-04 15:45:30  2000  04    15    30     
+---------------------+------+------+------+--------+

Formatting functions allow you to extract more than one part of a value. For example, to extract the entire date or time from DATETIME values, do this:

mysql>

SELECT dt,

->

DATE_FORMAT(dt,'%Y-%m-%d') AS 'date part',

->

TIME_FORMAT(dt,'%T') AS 'time part'

->

FROM datetime_val;

+---------------------+------------+-----------+
 dt                   date part   time part 
+---------------------+------------+-----------+
 1970-01-01 00:00:00  1970-01-01  00:00:00  
 1987-03-05 12:30:15  1987-03-05  12:30:15  
 1999-12-31 09:00:00  1999-12-31  09:00:00  
 2000-06-04 15:45:30  2000-06-04  15:45:30  
+---------------------+------------+-----------+

One advantage of using formatting functions is that you can display the extracted values in a different form than that in which they're present in the original values. If you want to present a date differently than in CCYY-MM-DD format or present a time without the seconds part, that's easy to do:

mysql>

SELECT ts,

->

DATE_FORMAT(ts,'%M %e, %Y') AS 'descriptive date',

->

TIME_FORMAT(ts,'%H:%i') AS 'hours/minutes'

->

FROM timestamp_val;

+----------------+-------------------+---------------+
 ts              descriptive date   hours/minutes 
+----------------+-------------------+---------------+
 19700101000000  January 1, 1970    00:00         
 19870305123015  March 5, 1987      12:30         
 19991231090000  December 31, 1999  09:00         
 20000604154530  June 4, 2000       15:45         
+----------------+-------------------+---------------+

5.5.4 See Also

Recipe 5.6 discusses other functions that may be used to extract single components from dates or times. Recipe 5.7 shows how to use substring functions for component extraction.