 |  |
16.3. Date Data Types
MySQL date types are extremely flexible tools for storing date information. They are also extremely forgiving in the belief that it is up to the application, not the database, to validate date values. MySQL only checks that months range from 0 to 12 and dates range from 0 to 31. February 31, 2001, is therefore a legal MySQL date. More useful, however, is the fact that February 0, 2001, is a legal date. In other words, you can use 0 to signify dates in which you do not know a particular piece of the date.
Though MySQL is somewhat forgiving on the input format, you should actually attempt to format all date values in your applications in MySQL's native format to avoid any confusion. MySQL always expects the year to be the left-most element of a date format. If you assign an illegal value in an SQL operation, MySQL will insert a zero for that value.
MySQL will also perform automatic conversion of date and time values to integer values when used in an integer context.
| DATE |
Syntax
DATE
Format
YYYY-MM-DD (2001-01-01)
Storage
3 bytes
Description
Stores a date in the range of January 1, 1000 ('1000-01-01') to December 31, 9999 ('9999-12-31') in the Gregorian calendar.
| DATETIME |
Syntax
DATETIME
Format
YYYY-MM-DD hh:mm:ss (2001-01-01 01:00:00)
Storage
8 bytes
Description
Stores a specific time in the range of 12:00:00 AM, January 1, 1000 ('1000-01-01 00:00:00') to 11:59:59 P.M., December 31, 9999 ('9999-12-31 23:59:59') in the Gregorian calendar.
| TIME |
Syntax
TIME
Format
hh:mm:ss (06:00:00)
Storage
3 bytes
Description
Stores a time value in the range of midnight ('00:00:00') to one second before midnight ('23:59:59').
| TIMESTAMP |
Syntax
TIMESTAMP[(display_size)]
Format
YYYYMMDDhhmmss (20010101060000)
Storage
4 bytes
Description
A simple representation of a point in time down to the second in the range of midnight on January 1, 1970, to one minute before midnight on December 31, 2037. Its primary utility is keeping track of table modifications. When you insert a NULL value into a TIMESTAMP column, the current date and time are inserted instead. When you modify any value in a row with a TIMESTAMP column, the first TIMESTAMP column will be automatically updated with the current date and time.
| YEAR |
Syntax
YEAR
Format
YYYY (2001)
Storage
1 byte
Description
Stores a year of the Gregorian calendar in the range of 1900 to 2155.
|  | |
| 16.2. String Data Types |  | 16.4. Complex Data Types |
Copyright © 2003 O'Reilly & Associates. All rights reserved.