GNU Info

Info Node: (mysql.info)Date and time types

(mysql.info)Date and time types


Next: String types Prev: Numeric types Up: Column types
Enter node , (file) or (file)node

Date and Time Types
-------------------

Y2K issues
Y2K issues and date types
DATETIME
The `DATETIME', `DATE' and `TIMESTAMP' types
TIME
The `TIME' type
YEAR
The `YEAR' type
The date and time types are `DATETIME', `DATE', `TIMESTAMP', `TIME',
and `YEAR'.  Each of these has a range of legal values, as well as a
"zero" value that is used when you specify a really illegal value.
Note that MySQL allows you to store certain 'not strictly' legal date
values, for example `1999-11-31'.  The reason for this is that we think
it's the responsibility of the application to handle date checking, not
the SQL servers.  To make the date checking 'fast', MySQL only checks
that the month is in the range of 0-12 and the day is in the range of
0-31. The above ranges are defined this way because MySQL allows you to
store, in a `DATE' or `DATETIME' column, dates where the day or
month-day is zero.  This is extremely useful for applications that need
to store a birth-date for which you don't know the exact date. In this
case you simply store the date like `1999-00-00' or `1999-01-00'.  (You
cannot expect to get a correct value from functions like `DATE_SUB()'
or `DATE_ADD' for dates like these.)

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
     format, but it attempts to interpret a variety of formats for
     values that you supply (for example, when you specify a value to
     be assigned to or compared to a date or time type).  Nevertheless,
     only the formats described in the following sections are
     supported.  It is expected that you will supply legal values, and
     unpredictable results may occur if you use values in other formats.

   * Although MySQL tries to interpret values in several formats, it
     always expects the year part of date values to be leftmost.  Dates
     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 (see the start 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 table below
     shows the format of the "zero" value for each type:

     *Column type*        *"Zero" value*
     `DATETIME'           `'0000-00-00 00:00:00''
     `DATE'               `'0000-00-00''
     `TIMESTAMP'          `00000000000000' (length depends on display
                          size)
     `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* Version 2.50.12 and above,
     because ODBC can't handle such values.


automatically generated by info2www version 1.2.2.9