(mysql.info) datetime

 
 11.3.1 The `DATETIME', `DATE', and `TIMESTAMP' Types
 ----------------------------------------------------

 
* timestamp-4-1                `TIMESTAMP' Properties as of MySQL 4.1
 
 The `DATETIME', `DATE', and `TIMESTAMP' types are related. This section
 describes their characteristics, how they are similar, and how they
 differ.
 
 The `DATETIME' type is used when you need values that contain both date
 and time information. MySQL retrieves and displays `DATETIME' values in
 `'YYYY-MM-DD HH:MM:SS'' format. The supported range is `'1000-01-01
 00:00:00'' to `'9999-12-31 23:59:59''.  (`Supported' means that
 although earlier values might work, there is no guarantee)
 
 The `DATE' type is used when you need only a date value, without a time
 part. MySQL retrieves and displays `DATE' values in `'YYYY-MM-DD''
 format. The supported range is `'1000-01-01'' to `'9999-12-31''.
 
 The `TIMESTAMP' data type has varying properties, depending on the
 MySQL version and the SQL mode the server is running in. These
 properties are described later in this section.
 
 You can specify `DATETIME', `DATE', and `TIMESTAMP' values using any of
 a common set of formats:
 
    * As a string in either `'YYYY-MM-DD HH:MM:SS'' or `'YY-MM-DD
      HH:MM:SS'' format. A `relaxed' syntax is allowed: Any punctuation
      character may be used as the delimiter between date parts or time
      parts. For example, `'98-12-31 11:30:45'', `'98.12.31 11+30+45'',
      `'98/12/31 11*30*45'', and `'98@12@31 11^30^45'' are equivalent.
 
    * As a string in either `'YYYY-MM-DD'' or `'YY-MM-DD'' format. A
      `relaxed' syntax is allowed here, too. For example, `'98-12-31'',
      `'98.12.31'', `'98/12/31'', and `'98@12@31'' are equivalent.
 
    * As a string with no delimiters in either `'YYYYMMDDHHMMSS'' or
      `'YYMMDDHHMMSS'' format, provided that the string makes sense as a
      date. For example, `'19970523091528'' and `'970523091528'' are
      interpreted as `'1997-05-23 09:15:28'', but `'971122129015'' is
      illegal (it has a nonsensical minute part) and becomes `'0000-00-00
      00:00:00''.
 
    * As a string with no delimiters in either `'YYYYMMDD'' or `'YYMMDD''
      format, provided that the string makes sense as a date. For
      example, `'19970523'' and `'970523'' are interpreted as
      `'1997-05-23'', but `'971332'' is illegal (it has nonsensical
      month and day parts) and becomes `'0000-00-00''.
 
    * As a number in either `YYYYMMDDHHMMSS' or `YYMMDDHHMMSS' format,
      provided that the number makes sense as a date. For example,
      `19830905132800' and `830905132800' are interpreted as
      `'1983-09-05 13:28:00''.
 
    * As a number in either `YYYYMMDD' or `YYMMDD' format, provided that
      the number makes sense as a date. For example, `19830905' and
      `830905' are interpreted as `'1983-09-05''.
 
    * As the result of a function that returns a value that is
      acceptable in a `DATETIME', `DATE', or `TIMESTAMP' context, such
      as `NOW()' or `CURRENT_DATE'.
 
 Illegal `DATETIME', `DATE', or `TIMESTAMP' values are converted to the
 `zero' value of the appropriate type (`'0000-00-00 00:00:00'' or
 `'0000-00-00'').
 
 For values specified as strings that include date part delimiters, it
 is not necessary to specify two digits for month or day values that are
 less than `10'.  `'1979-6-9'' is the same as `'1979-06-09''. Similarly,
 for values specified as strings that include time part delimiters, it
 is not necessary to specify two digits for hour, minute, or second
 values that are less than `10'.  `'1979-10-30 1:2:3'' is the same as
 `'1979-10-30 01:02:03''.
 
 Values specified as numbers should be 6, 8, 12, or 14 digits long. If a
 number is 8 or 14 digits long, it is assumed to be in `YYYYMMDD' or
 `YYYYMMDDHHMMSS' format and that the year is given by the first 4
 digits. If the number is 6 or 12 digits long, it is assumed to be in
 `YYMMDD' or `YYMMDDHHMMSS' format and that the year is given by the
 first 2 digits. Numbers that are not one of these lengths are
 interpreted as though padded with leading zeros to the closest length.
 
 Values specified as non-delimited strings are interpreted using their
 length as given. If the string is 8 or 14 characters long, the year is
 assumed to be given by the first 4 characters.  Otherwise, the year is
 assumed to be given by the first 2 characters. The string is
 interpreted from left to right to find year, month, day, hour, minute,
 and second values, for as many parts as are present in the string. This
 means you should not use strings that have fewer than 6 characters. For
 example, if you specify `'9903'', thinking that represents March, 1999,
 MySQL inserts a `zero' date value into your table. This occurs because
 the year and month values are `99' and `03', but the day part is
 completely missing, so the value is not a legal date.  However, you can
 explicitly specify a value of zero to represent missing month or day
 parts. For example, you can use `'990300'' to insert the value
 `'1999-03-00''.
 
 You can to some extent assign values of one date type to an object of a
 different date type. However, there may be some alteration of the value
 or loss of information:
 
    * If you assign a `DATE' value to a `DATETIME' or `TIMESTAMP'
      object, the time part of the resulting value is set to
      `'00:00:00'' because the `DATE' value contains no time information.
 
    * If you assign a `DATETIME' or `TIMESTAMP' value to a `DATE'
      object, the time part of the resulting value is deleted because the
      `DATE' type stores no time information.
 
    * Remember that although `DATETIME', `DATE', and `TIMESTAMP' values
      all can be specified using the same set of formats, the types do
      not all have the same range of values. For example, `TIMESTAMP'
      values cannot be earlier than `1970' or later than `2037'. This
      means that a date such as `'1968-01-01'', while legal as a
      `DATETIME' or `DATE' value, is not valid as a `TIMESTAMP' value
      and is converted to `0'.
 
 Be aware of certain pitfalls when specifying date values:
 
    * The relaxed format allowed for values specified as strings can be
      deceiving. For example, a value such as `'10:11:12'' might look
      like a time value because of the ‘`:'’ delimiter, but if used
      in a date context is interpreted as the year `'2010-11-12''. The
      value `'10:45:15'' is converted to `'0000-00-00'' because `'45''
      is not a legal month.
 
    * As of 5.0.2, the server requires that month and day values be
      legal, and not merely in the range 1 to 12 and 1 to 31,
      respectively. With strict mode disabled, invalid dates such as
      `'2004-04-31'' are converted to `'0000-00-00'' and a warning is
      generated.  With strict mode enabled, invalid dates generate an
      error.  To allow such dates, enable `ALLOW_INVALID_DATES'. See
       server-sql-mode, for more information.
 
      Before MySQL 5.0.2, the MySQL server performs only basic checking
      on the validity of a date: The ranges for year, month, and day are
      1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date
      containing parts not within these ranges is subject to conversion
      to `'0000-00-00''. Please note that this still allows you to store
      invalid dates such as `'2002-04-31''. To ensure that a date is
      valid, you should perform a check in your application.
 
    * 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'.

Info Catalog

(mysql.info) date-and-time-types

(mysql.info) time

automatically generated byinfo2html

(mysql.info) datetime

Menu