content_image/784323.jpg
any TIMESTAMP  column to the current date and time by assigning it a NULL  value, unless it has been defined with the NULL  attribute to permit NULL  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 displays TIME values in ’ hh:mm:ss [. fraction ]’  format, but permits assignment of values to TIME  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 displays YEAR  values in YYYY  format, but permits assignment of values to YEAR  columns using either strings or numbers. Values display as 1901  to 2155 , or 0000 . 

For additional information about YEAR  display format and interpretation of input values, see Section 11.2.4, “The YEAR Type” . 

<img src='content_image/784323.jpg'>

## Note 

As of MySQL 8.0.19, the YEAR(4)  data type with an explicit display width is deprecated; you should expect support for it to be removed in a future version of MySQL. Instead, use YEAR  without a display width, which has the same meaning. 

MySQL 8.0 does not support the 2-digit YEAR(2)  data type permitted in older versions of MySQL. For instructions on converting to 4-digit YEAR , see 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR, in MySQL 5.7 Reference Manual. 

The SUM()  and AVG()  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: 

## 11.2.2 The DATE, DATETIME, and TIMESTAMP Types 

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

The DATE , DATETIME , and TIMESTAMP  types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE , DATETIME , and TIMESTAMP  values in several formats, described in Section 9.1.3, “Date and Time Literals” . For the DATE  and DATETIME  range descriptions, “supported”  means that although earlier values might work, there is no guarantee. 

The DATE  type is used for values with a date part but no 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 DATETIME  type is used for values that contain both date and time parts. 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’ . 

