The DATE, DATETIME, and TIMESTAMP types are related. DATE、DATETIME和TIMESTAMP类型是相关的。This section describes their characteristics, how they are similar, and how they differ. 本节介绍了它们的特点、相似之处以及不同之处。MySQL recognizes MySQL以几种格式识别DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. DATE、DATETIME和TIMESTAMP，如第9.1.3节“日期和时间文本”所述。For the 对于DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.DATE和DATETIME范围的描述，“支持”意味着尽管早期的值可能有效，但不能保证。
The DATE type is used for values with a date part but no time part. DATE类型用于有日期部分但没有时间部分的值。MySQL retrieves and displays MySQL以DATE values in ' format. YYYY-MM-DD''格式检索和显示日期值。YYYY-MM-DD'The supported range is 支持的范围是'1000-01-01' to '9999-12-31'.'1000-01-01'到'9999-12-31'。
The DATETIME type is used for values that contain both date and time parts. DATETIME类型用于同时包含日期和时间部分的值。MySQL retrieves and displays MySQL以DATETIME values in ' format. YYYY-MM-DD hh:mm:ss''的格式检索并显示YYYY-MM-DD hh:mm:ss'DATETIME值。The supported range is 支持的范围是'1000-01-01 00:00:00' to '9999-12-31 23:59:59'.'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP数据类型用于同时包含日期和时间部分的值。TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.TIMESTAMP的范围为'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'UTC。
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. DATETIME或TIMESTAMP值可以包含精度高达微秒（6位）的尾随小数秒部分。In particular, any fractional part in a value inserted into a 特别地，插入到DATETIME or TIMESTAMP column is stored rather than discarded. DATETIME或TIMESTAMP列中的值中的任何小数部分都将被存储而不是丢弃。With the fractional part included, the format for these values is 包括小数部分后，这些值的格式为', the range for YYYY-MM-DD hh:mm:ss[.fraction]'DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. '，日期时间值的范围为YYYY-MM-DD hh:mm:ss[.fraction]''1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'，时间戳值的范围为'1970-01-01 00:00:01.000000'到'2038-01-19 03:14:07.999999'。The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. 小数部分应始终与其余时间间隔一个小数点；无法识别其他小数秒分隔符。For information about fractional seconds support in MySQL, see Section 11.2.6, “Fractional Seconds in Time Values”.有关MySQL中分数秒支持的信息，请参阅第11.2.6节“时间值中的小数秒”。
The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. TIMESTAMP和DATETIME数据类型提供对当前日期和时间的自动初始化和更新。For more information, see Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.有关更多信息，请参阅第11.2.5节“时间戳和日期时间的自动初始化和更新”。
MySQL converts MySQL将TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. TIMESTAMPS值从当前时区转换为UTC进行存储，然后从UTC转换回当前时区进行检索。(This does not occur for other types such as （其他类型（如DATETIME.) DATETIME)不会出现这种情况。）By default, the current time zone for each connection is the server's time. 默认情况下，每个连接的当前时区是服务器的时间。The time zone can be set on a per-connection basis. 时区可以根据每个连接设置。As long as the time zone setting remains constant, you get back the same value you store. 只要时区设置保持不变，就可以恢复存储的值。If you store a 如果存储TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. TIMESTAMP值，然后更改时区并检索该值，则检索到的值与存储的值不同。This occurs because the same time zone was not used for conversion in both directions. 发生这种情况是因为在两个方向上没有使用同一时区进行转换。The current time zone is available as the value of the 当前时区可用作time_zone system variable. time_zone系统变量的值。For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.有关更多信息，请参阅第5.1.15节“MySQL服务器时区支持”。
As of MySQL 8.0.19, you can specify a time zone offset when inserting 从MySQL 8.0.19开始，在向表中插入TIMESTAMP and DATETIME values into a table. TIMESTAMP和DATETIME值时，可以指定时区偏移量。The offset is appended to the time part of a datetime literal, with no intravening spaces, and uses the same format used for setting the 偏移量附加到日期时间文本的时间部分，不带空格，并使用与设置time_zone system variable, with the following exceptions:time_zone系统变量相同的格式，但以下情况除外：
For hour values less than than 10, a leading zero is required.对于小于10的小时值，需要前导零。
The value 值'-00:00' is rejected.'-00:00'被拒绝。
Time zone names such as 不能使用'EET' and 'Asia/Shanghai' cannot be used; 'SYSTEM' also cannot be used in this context.'EET'和'Asia/Shanghai'等时区名称；'SYSTEM'也不能在此上下文中使用。
The value inserted must not have a zero for the month part, the day part, or both parts. 插入的值的月份部分、日部分或两者都不能为零。This is enforced beginning with MySQL 8.0.22, regardless of the server SQL mode setting.这是从MySQL8.0.22开始强制执行的，不管服务器SQL模式设置如何。
This example illustrates inserting datetime values with time zone offsets into 此示例演示如何使用不同的TIMESTAMP and DATETIME columns using different time_zone settings, and then retrieving them:time_zone设置将带有时区偏移的日期时间值插入TIMESTAMP和DATETIME列，然后检索它们：
mysql>CREATE TABLE ts (->id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,->col TIMESTAMP NOT NULL->) AUTO_INCREMENT = 1;mysql>CREATE TABLE dt (->id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,->col DATETIME NOT NULL->) AUTO_INCREMENT = 1;mysql>SET @@time_zone = 'SYSTEM';mysql>INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = '+00:00';mysql>INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = 'SYSTEM';mysql>INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = '+00:00';mysql>INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = 'SYSTEM';mysql>SELECT @@system_time_zone;+--------------------+ | @@system_time_zone | +--------------------+ | EST | +--------------------+ mysql>SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;+---------------------+---------------------+ | col | UNIX_TIMESTAMP(col) | +---------------------+---------------------+ | 2020-01-01 10:10:10 | 1577891410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | | 2020-01-01 10:10:10 | 1577891410 | | 2020-01-01 04:40:10 | 1577871610 | | 2020-01-01 18:10:10 | 1577920210 | +---------------------+---------------------+ mysql>SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;+---------------------+---------------------+ | col | UNIX_TIMESTAMP(col) | +---------------------+---------------------+ | 2020-01-01 10:10:10 | 1577891410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | | 2020-01-01 05:10:10 | 1577873410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | +---------------------+---------------------+
The offset is not displayed when selecting a datetime value, even if one was used when inserting it.选择日期时间值时不显示偏移量，即使插入时使用了偏移量。
The range of supported offset values is 支持的偏移值范围为-13:59 to +14:00, inclusive.-13:59到+14:00(含）。
Datetime literals that include time zone offsets are accepted as parameter values by prepared statements.包含时区偏移量的Datetime文本被准备好的语句接受为参数值。
Invalid 如果SQL模式允许此转换，则将无效的DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. DATE、DATETIME或TIMESTAMP值转换为相应类型的“零”值（'0000-00-00'或'0000-00-00 00:00:00'）。The precise behavior depends on which if any of strict SQL mode and the 精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.NO_ZERO_DATESQL模式；请参阅第5.1.11节“服务器SQL模式”。
In MySQL 8.0.22 and later, you can convert 在MySQL 8.0.22及更高版本中，使用TIMESTAMP values to UTC DATETIME values when retrieving them using CAST() with the AT TIME ZONE operator, as shown here:CAST()和AT TIME ZONE操作符检索时，可以将TIMESTAMP值转换为UTCDATETIME值，如下所示：
mysql>SELECT col,>CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut>FROM ts ORDER BY id;+---------------------+---------------------+ | col | ut | +---------------------+---------------------+ | 2020-01-01 10:10:10 | 2020-01-01 15:10:10 | | 2019-12-31 23:40:10 | 2020-01-01 04:40:10 | | 2020-01-01 13:10:10 | 2020-01-01 18:10:10 | | 2020-01-01 10:10:10 | 2020-01-01 15:10:10 | | 2020-01-01 04:40:10 | 2020-01-01 09:40:10 | | 2020-01-01 18:10:10 | 2020-01-01 23:10:10 | +---------------------+---------------------+
For complete information regarding syntax and additional examples, see the description of the 有关语法和其他示例的完整信息，请参阅CAST() function.CAST()函数的说明。
Be aware of certain properties of date value interpretation in MySQL:请注意MySQL中日期值解释的某些属性：
MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. MySQL允许将值指定为字符串的“宽松”格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。In some cases, this syntax can be deceiving. 在某些情况下，这种语法可能具有欺骗性。For example, a value such as 例如，'10:11:12' might look like a time value because of the :, but is interpreted as the year '2010-11-12' if used in date context. '10:11:12'这样的值可能看起来像时间值，因为:，但如果在日期上下文中使用，则会被解释为年份'2010-11-12'。The value 值'10:45:15' is converted to '0000-00-00' because '45' is not a valid month.'10:45:15'已转换为'0000-00-00'，因为“45”不是有效的月份。
The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.在日期和时间部分和小数秒部分之间唯一可以识别的分隔符是小数点。
The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. 服务器要求月和日的值有效，而不仅仅是分别在1到12和1到31之间。With strict mode disabled, invalid dates such as 禁用严格模式时，无效日期（如'2004-04-31' are converted to '0000-00-00' and a warning is generated. '2004-04-31'）将转换为'0000-00-00'，并生成警告。With strict mode enabled, invalid dates generate an error. 启用严格模式时，无效日期将生成错误。To permit such dates, enable 若要允许此类日期，请启用ALLOW_INVALID_DATES. ALLOW_INVALID_DATES。See Section 5.1.11, “Server SQL Modes”, for more information.有关详细信息，请参阅第5.1.11节“服务器SQL模式”。
MySQL does not accept MySQL不接受在“日”或“月”列中包含零的TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. TIMESTAMP值或无效日期的值。The sole exception to this rule is the special “zero” value 此规则的唯一例外是特殊的“零”值'0000-00-00 00:00:00', if the SQL mode permits this value. '0000-00-00:00:00'，如果SQL模式允许此值。The precise behavior depends on which if any of strict SQL mode and the 精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE SQL mode are enabled; see Section 5.1.11, “Server SQL Modes”.NO_ZERO_DATESQL模式；请参阅第5.1.11节“服务器SQL模式”。
Dates containing 2-digit year values are ambiguous because the century is unknown. 包含两位数年份值的日期不明确，因为世纪未知。MySQL interprets 2-digit year values using these rules:MySQL使用以下规则解释两位数的年份值：
Year values in the range 范围为00-69 become 2000-2069.00-69的年份值变为2000-20692000-2069。
Year values in the range 70-99 become 1970-1999.70-99之间的年份值变为1970-1999。
See also Section 11.2.8, “2-Digit Years in Dates”.另请参阅第11.2.8节，“日期中的两位数年份”。