(mysql.info) date-and-time-types
Info Catalog
(mysql.info) numeric-types
(mysql.info) data-types
(mysql.info) string-types
11.3 Date and Time Types
========================
Menu
* datetime The `DATETIME', `DATE', and `TIMESTAMP' Types
* time The `TIME' Type
* year The `YEAR' Type
* y2k-issues Y2K Issues and Date Types
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 may be used
when you specify an illegal value that MySQL cannot represent. The
`TIMESTAMP' type has special automatic updating behavior, described
later on. For temporary type storage requirements, see
storage-requirements.
Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to
insert an illegal date. By setting the SQL mode to the appropriate
value, you can specify more exactly what kind of dates you want MySQL
to support. (See server-sql-mode.) You can get MySQL to accept
certain dates, such as `'1999-11-31'', by using the
`ALLOW_INVALID_DATES' SQL mode. (Before 5.0.2, this mode was the
default behavior for MySQL.) This is useful when you want to store a
`possibly wrong' value which the user has specified (for example, in a
web form) in the database for future processing. Under this mode, 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
`'1999-00-00'' or `'1999-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. (If you do
_not_ want to allow zero in dates, you can use the `NO_ZERO_IN_DATE'
SQL mode).
MySQL also allows you to store `'0000-00-00'' as a `dummy date' (if you
are not using the `NO_ZERO_DATE' SQL mode). This is in some cases is
more convenient (and uses less space in data and index) than using
`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 `70-99' are converted to `1970-1999'.
* Year values in the range `00-69' are converted to `2000-2069'.
* 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 following table shows the format of the `zero' value for each
type. Note that the use of these values produces warnings if the
`NO_ZERO_DATE' SQL mode is enabled.
*Data Type* *`Zero' Value*
`DATETIME' `'0000-00-00 00:00:00''
`DATE' `'0000-00-00''
`TIMESTAMP' `'0000-00-00 00:00:00''
`TIME' `'00:00:00''
`YEAR' `0000'
* 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.
Info Catalog
(mysql.info) numeric-types
(mysql.info) data-types
(mysql.info) string-types
automatically generated byinfo2html