(mysql.info.gz) Date and time types

 
 11.3 Date and Time Types
 ========================

 
* 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 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 will give 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.   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 will 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-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.
 
    * 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.
 
      *Column Type*        *"Zero" Value*
      `DATETIME'           `'0000-00-00 00:00:00''
      `DATE'               `'0000-00-00''
      `TIMESTAMP'          `00000000000000'
      `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
      can't handle such values.

Info Catalog

(mysql.info.gz) Numeric types

(mysql.info.gz) Column types

(mysql.info.gz) String types

automatically generated byinfo2html

(mysql.info.gz) Date and time types

Menu