13.2.1 Date and Time Data Type Syntax (original) (raw)

13.2.1 Date and Time Data Type Syntax

The date and time data types for representing temporal values are DATE,TIME,DATETIME,TIMESTAMP, andYEAR.

For the DATE andDATETIME range descriptions,“supported” means that although earlier values might work, there is no guarantee.

MySQL permits fractional seconds forTIME,DATETIME, andTIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax_`typename`_(_`fsp`_), where typename isTIME,DATETIME, orTIMESTAMP, and_fsp_ is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

Any TIMESTAMP orDATETIME column in a table can have automatic initialization and updating properties; seeSection 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

• DATE
  A date. The supported range is'1000-01-01' to'9999-12-31'. MySQL displaysDATE values in'_`YYYY-MM-DD`_' format, but permits assignment of values toDATE columns using either strings or numbers.
• DATETIME[(fsp)]
  A date and time combination. The supported range is'1000-01-01 00:00:00.000000' to'9999-12-31 23:59:59.499999'. MySQL displays DATETIME values in'_`YYYY-MM-DD hh:mm:ss`_[._`fraction`_]' format, but permits assignment of values toDATETIME columns using either strings or numbers.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
  Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT andON UPDATE column definition clauses, as described in Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
• TIMESTAMP[(fsp)]
  A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.499999' UTC.TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). ATIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
  The way the server handles TIMESTAMP definitions depends on the value of theexplicit_defaults_for_timestamp system variable (seeSection 7.1.8, “Server System Variables”).
  Ifexplicit_defaults_for_timestamp is enabled, there is no automatic assignment of theDEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to anyTIMESTAMP column. They must be included explicitly in the column definition. Also, anyTIMESTAMP not explicitly declared as NOT NULL permitsNULL values.
  Ifexplicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:
  Unless specified otherwise, the firstTIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of anINSERT orUPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it aNULL value, unless it has been defined with the NULL attribute to permitNULL values.
  Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.
• TIME[(fsp)]
  A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displaysTIME values in'_`hh:mm:ss`_[._`fraction`_]' format, but permits assignment of values toTIME columns using either strings or numbers.
  An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
• YEAR[(4)]
  A year in 4-digit format. MySQL displaysYEAR values in_YYYY_ format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as1901 to 2155, or0000.
  For additional information aboutYEAR display format and interpretation of input values, see Section 13.2.4, “The YEAR Type”.

The SUM() andAVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;