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

表示时间值的日期和时间数据类型有date、time、DATETIME、TIMESTAMP和YEAR。

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

对于DATE和DATETIME范围描述，“受支持”意味着尽管较早的值可以工作，但不能保证。

MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

MySQL允许对TIME、DATETIME和TIMESTAMP值使用小数秒，精度最高可达微秒(6位)。要定义包含小数秒部分的列，使用语法type_name(fsp)，其中type_name是TIME、DATETIME或TIMESTAMP，而fsp是小数秒精度。例如:

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.)

fsp的值，如果给定，必须在0 ~ 6之间。值为0表示没有小数部分。如果省略，默认精度为0。(这与标准SQL默认值6不同，这是为了与以前的MySQL版本兼容。)

Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating properties; see Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性;

DATE

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.

一个日期。支持的范围是“1000-01-01”到“99999 -12-31”。MySQL以“YYYY-MM-DD”格式显示DATE值，但允许使用字符串或数字向DATE列赋值。

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.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD hh:mm:ss[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.

日期和时间的组合。支持的范围是“1000-01-01 00:00:00.000000”到“99999-12-31 23:59:59.999999”。MySQL以“YYYY-MM-DD hh:mm:ss[.fraction]”的格式显示DATETIME值，但允许使用字符串或数字给DATETIME列赋值。

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.

一个可选的范围从0到6的fsp值可以指定分数秒精度。值为0表示没有小数部分。如果省略，默认精度为0。

Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

DATETIME列的自动初始化和更新到当前日期和时间可以使用DEFAULT和ON UPDATE列定义子句指定，如11.2.6节“TIMESTAMP和DATETIME的自动初始化和更新”所述。

TIMESTAMP[(fsp)]

A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP 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.

一个时间戳。取值范围是'1970-01-01 00:00:01.000000' UTC到'2038-01-19 03:14:07.999999' UTC。TIMESTAMP值存储为自纪元('1970-01-01 00:00:00' UTC)以来的秒数。TIMESTAMP不能表示值'1970-01-01 00:00:00'，因为这相当于从epoch开始的0秒，而0被保留用于表示'0000-00-00 00:00:00'，即TIMESTAMP的" 0 "值。

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.

一个可选的范围从0到6的fsp值可以指定分数秒精度。值为0表示没有小数部分。如果省略，默认精度为0。

The way the server handles TIMESTAMP definitions depends on the value of the explicit_defaults_for_timestamp system variable (see Section 5.1.7, “Server System Variables”).

服务器处理TIMESTAMP定义的方式取决于explicit_defaults_for_timestamp系统变量的值(参见第5.1.7节，“服务器系统变量”)。

If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to any TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP not explicitly declared as NOT NULL permits NULL values.

如果启用了explicit_defaults_for_timestamp，则不会自动将DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性赋给任何TIMESTAMP列。它们必须显式地包含在列定义中。此外，任何未显式声明为not NULL的TIMESTAMP都允许NULL值。

If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:

如果禁用了explicit_defaults_for_timestamp，服务器将按照如下方式处理TIMESTAMP:

Unless specified otherwise, the first TIMESTAMP 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 an INSERT or UPDATE operation. You can also set 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.

除非另有指定，否则表中的第一个TIMESTAMP列定义为如果没有显式地分配值，则自动设置为最近修改的日期和时间。这使得TIMESTAMP用于记录INSERT或UPDATE操作的时间戳。您还可以通过给任何TIMESTAMP列赋一个NULL值来将其设置为当前日期和时间，除非它已经用NULL属性定义以允许NULL值。

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.

可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句指定自动初始化和更新到当前日期和时间。如前所述，默认情况下，第一个TIMESTAMP列具有这些属性。但是，表中的任何TIMESTAMP列都可以定义为具有这些属性。

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.

一段时间。取值范围为“-838:59:59 000000”到“838:59:59 000000”。MySQL以'hh:mm:ss[.fraction]'的格式显示TIME值，但允许使用字符串或数字给TIME列赋值。

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.

一个可选的范围从0到6的fsp值可以指定分数秒精度。值为0表示没有小数部分。如果省略，默认精度为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.

4位格式的一年。MySQL以YYYY格式显示YEAR值，但允许使用字符串或数字向YEAR列赋值。值显示为1901到2155，或0000。

Note

The YEAR(2) data type is deprecated and support for it is removed in MySQL 5.7.5. To convert 2-digit YEAR(2) columns to 4-digit YEAR columns, see Section 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.

YEAR(2)数据类型已弃用，在MySQL 5.7.5中已移除对它的支持。要将2位YEAR(2)列转换为4位YEAR列，请参见11.2.5节“2位YEAR(2)限制和迁移到4位YEAR”。

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

有关YEAR显示格式和输入值解释的更多信息，请参见11.2.4节“YEAR类型”。

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:

SUM()和AVG()聚合函数不能处理时间值。(它们将值转换为数字，失去第一个非数字字符之后的所有内容。)要解决这个问题，请将其转换为数字单元，执行聚合操作，并将其转换回时间值。例子:

Note

The MySQL server can be run with the MAXDB SQL mode enabled. In this case, TIMESTAMP is identical with DATETIME. If this mode is enabled at the time that a table is created, TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and there is no automatic initialization or updating to the current date and time. See Section 5.1.10, “Server SQL Modes”.

MySQL服务器可以在MAXDB SQL模式下运行。在本例中，TIMESTAMP与DATETIME相同。如果在创建表时启用了此模式，则TIMESTAMP列将被创建为DATETIME列。因此，这些列使用DATETIME显示格式，具有相同的值范围，并且不会自动初始化或更新到当前日期和时间。参见5.1.10节“服务器SQL模式”。

Note

As of MySQL 5.7.22, MAXDB is deprecated; expect it to removed in a future version of MySQL.

从MySQL 5.7.22开始，MAXDB已弃用;希望在未来的MySQL版本中删除它。