:: DEVELOPER ZONE
MySQL Reference Manual :: 11.3 Date and Time Types
Search the MySQL manual:
- 11 Column Types
- 11.1 Column Type Overview
- 11.2 Numeric Types
- 11.3 Date and Time Types
- 11.4 String Types
- 11.5 Column Type Storage Requirements
- 11.6 Choosing the Right Type for a Column
- 11.7 Using Column Types from Other Database Engines
Additional languages
Additional formats
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.
Starting from MySQL 5.0.2, MySQL gives warnings/errors if you try to
insert an illegal date. 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 the ``possibly wrong'' value 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 don't 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 don't 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.
By setting the sql_mode system variable to the
appropriate mode values, You can more exactly what kind of dates you
want MySQL to support. See Section 5.3.2, “The Server SQL Mode”.
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, and 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-69are converted to2000-2069.Year values in the range
70-99are converted to1970-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.
-
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
TIMEvalues are clipped to the appropriate endpoint of theTIMErange.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_DATESQL mode is enabled.Column Type ``Zero'' Value DATETIME'0000-00-00 00:00:00'DATE'0000-00-00'TIMESTAMP00000000000000TIME'00:00:00'YEAR0000 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'or0, which are easier to write.``Zero'' date or time values used through MyODBC are converted automatically to
NULLin MyODBC 2.50.12 and above, because ODBC can't handle such values.
© 1995-2005 MySQL AB. All rights reserved.
User Comments
Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245
Add your own comment.