- Documentation Library
- Table of Contents
- MySQL 5.7 Manual
- MySQL 5.6 Manual
- MySQL 5.5 Manual
- MySQL 5.1 Manual
- MySQL 5.0 Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
[+/-]
The date and time types for representing temporal values are
DATE,
TIME,
DATETIME,
TIMESTAMP, and
YEAR. Each temporal type has a
range of legal values, as well as a “zero” value that
is used when you specify an illegal value that MySQL cannot
represent. The TIMESTAMP type has
special automatic updating behavior, described later on. For
temporal type storage requirements, see
Section 10.5, “Data Type Storage Requirements”.
MySQL versions through 4.1 accept certain “illegal”
values for dates, such as '2009-11-31'. This is
useful when you want to store a possibly incorrect value specified
by a user (for example, in a web form) in the database for future
processing. MySQL verifies only that the month is in the range
from 0 to 12 and that the day is in the range from 0 to 31. These
ranges are defined to include zero because MySQL permits you to
store dates where the day or month and day are zero in a
DATE or
DATETIME column. This is extremely
useful for applications that need to store a birthdate for which
you do not know the exact date. In this case, you simply store the
date as '2009-00-00' or
'2009-01-00'. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB() or
DATE_ADD() that require complete
dates.
MySQL also permits you to store '0000-00-00' as
a “dummy date.” This is in some cases more convenient
(and uses less data and index space) than storing
NULL values.
Keep in mind these general considerations when working with date and time types:
MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see Section 8.1.3, “Date and Time Literals”. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example,
'98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example,'09-04-98','04-09-98').Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
Year values in the range
70-99are converted to1970-1999.Year values in the range
00-69are converted to2000-2069.
Conversion of values from one temporal type to another occurs according to the rules in Section 10.3.5, “Conversion Between Date and Time Types”.
MySQL automatically converts a date or time value to a number if the value is used in a numeric context and vice versa.
By default, when MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type, it converts the value to the “zero” value for that type. The exception is that out-of-range
TIMEvalues are clipped to the appropriate endpoint of theTIMErange.“Zero” date or time values used through MyODBC are converted automatically to
NULLin MyODBC 2.50.12 and above, because ODBC cannot handle such values.
The following table shows the format of the “zero”
value for each type. The “zero” values are special,
but you can store or refer to them explicitly using the values
shown in the table. You can also do this using the values
'0' or 0, which are easier
to write.
| Data Type | “Zero” Value |
|---|---|
DATE | '0000-00-00' |
TIME | '00:00:00' |
DATETIME | '0000-00-00 00:00:00' |
TIMESTAMP (4.1 and up) | '0000-00-00 00:00:00' |
TIMESTAMP (before 4.1) | 00000000000000 |
YEAR | 0000 |
User Comments
Add your own comment.