[+/-]
The date and time types for representing temporal values are
DATETIME
,
DATE
,
TIMESTAMP
,
TIME
, 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 allows 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 allows 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.
Here are some general considerations to keep in mind 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). Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 00-69
are
converted to 2000-2069
.
Year values in the range 70-99
are
converted to 1970-1999
.
Although MySQL tries to interpret values in several formats,
dates always must 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'
).
MySQL automatically converts a date or time type 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
(as described at the beginning of this section), it converts
the value to the “zero” value for that type. The
exception is that out-of-range
TIME
values are clipped to the
appropriate endpoint of the
TIME
range.
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.
“Zero” date or time values used through MyODBC
are converted automatically to NULL
in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
User Comments
Add your own comment.