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.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net