MySQL 5.7-11.2 Date and Time Data Types

11.2.1 Date and Time Data Type Syntax

11.2.2 The DATE, DATETIME, and TIMESTAMP Types

11.2.3 The TIME Type

11.2.4 The YEAR Type

11.2.5 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR

11.2.6 Automatic Initialization and Updating for TIMESTAMP and DATETIME

11.2.7 Fractional Seconds in Time Values

11.2.8 Conversion Between Date and Time Types

11.2.9 2-Digit Years in Dates

The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent. The TIMESTAMP and DATETIME types have special automatic updating behavior, described in Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

表示时间值的日期和时间数据类型有date、time、DATETIME、TIMESTAMP和YEAR。每个时态类型都有一个有效值范围，以及一个“零”值，当您指定一个MySQL不能表示的无效值时，可以使用这个“零”值。TIMESTAMP和DATETIME类型有特殊的自动更新行为，在第11.2.6节“TIMESTAMP和DATETIME的自动初始化和更新”中描述。

For information about storage requirements of the temporal data types, see Section 11.7, “Data Type Storage Requirements”.

有关时态数据类型的存储需求的信息，请参见第11.7节“数据类型存储需求”。

For descriptions of functions that operate on temporal values, see Section 12.7, “Date and Time Functions”.

有关作用于时间值的函数的描述，请参见第12.7节“日期和时间函数”。

Keep in mind these general considerations when working with date and time types:

在处理日期和时间类型时，请记住以下几点:

• MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see Section 9.1.3, “Date and Time Literals”. It is expected that you supply valid values. Unpredictable results may occur if you use values in other formats.

• MySQL以标准输出格式检索给定日期或时间类型的值，但它试图解释您提供的输入值的各种格式(例如，当您指定要分配给或比较日期或时间类型的值时)。有关日期和时间类型允许的格式的描述，请参见第9.1.3节“日期和时间文字”。您需要提供有效的值。如果使用其他格式的值，可能会出现不可预知的结果。

• Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98'). To convert strings in other orders to year-month-day order, the STR_TO_DATE() function may be useful.

• 尽管MySQL尝试用几种格式来解释值，但日期部分必须总是按年-月-日顺序给出(例如，'98-09-04')，而不是按其他地方常用的月-日-年或日-月-年顺序给出(例如，'09-04-98'，'04-09-98')。要将其他顺序的字符串转换为年-月-日顺序，STR_TO_DATE()函数可能很有用。

• Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:包含两位数年份值的日期是不明确的，因为世纪是未知的。MySQL使用以下规则解释2位数的年份值:

  • Year values in the range 70-99 become 1970-1999.

  • 年份值在70-99之间就变成1970-1999

  • Year values in the range 00-69 become 2000-2069.

  • 年值在00-69范围内变为2000-2069

  See also Section 11.2.9, “2-Digit Years in Dates”.

• Conversion of values from one temporal type to another occurs according to the rules in Section 11.2.8, “Conversion Between Date and Time Types”.

• 根据第11.2.8节“日期和时间类型之间的转换”中的规则，将值从一种时间类型转换为另一种时间类型。

• MySQL automatically converts a date or time value to a number if the value is used in numeric context and vice versa.

• MySQL自动将日期或时间值转换为数字，如果该值在数字上下文中使用，反之亦然。

• By default, when MySQL encounters a value for a date or time type that is out of range or otherwise invalid for the type, it converts the value to the “zero” value for that type. The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range.

• 默认情况下，当MySQL遇到超出范围或无效的日期或时间类型的值时，它会将该值转换为该类型的“零”值。例外情况是超出范围的TIME值被裁剪到时间范围的适当端点。

• By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.1.10, “Server SQL Modes”.) You can get MySQL to accept certain dates, such as '2009-11-31', by enabling the ALLOW_INVALID_DATES SQL mode. This is useful when you want to store a “possibly wrong” value which the user has specified (for example, in a web form) in the database for future processing. Under this mode, MySQL verifies only that the month is in the range from 1 to 12 and that the day is in the range from 1 to 31.

• 通过将SQL模式设置为适当的值，可以更准确地指定希望MySQL支持的日期类型。(参见5.1.10节，“服务器SQL模式”)你可以通过启用ALLOW_INVALID_DATES SQL模式来让MySQL接受某些日期，比如'2009-11-31'。当你想在数据库中存储用户指定的“可能错误的”值(例如，在web表单中)以备将来处理时，这是很有用的。在这种模式下，MySQL只验证月在1到12的范围内，日在1到31的范围内。

• MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. However, with dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable the NO_ZERO_IN_DATE mode.

• MySQL允许您在DATE或DATETIME列中存储日或月和日为零的日期。这对于需要存储您可能不知道确切日期的生日的应用程序非常有用。在本例中，您只需将日期存储为'2009-00-00'或'2009-01-00'。但是，对于DATE_SUB()或DATE_ADD()等需要完整日期的函数，您不应该期望得到正确的结果。若要禁止在日期中包含零个月或天，请启用NO_ZERO_IN_DATE模式。

• MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” In some cases, this is more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE mode.

• MySQL允许您将0值'0000-00-00'存储为“虚拟日期”。在某些情况下，这比使用NULL值更方便，并且使用更少的数据和索引空间。要禁用'0000-00-00'，请启用NO_ZERO_DATE模式。

• “Zero” date or time values used through Connector/ODBC are converted automatically to NULL because ODBC cannot handle such values.

• 通过Connector/ODBC使用的“零”日期或时间值会自动转换为NULL，因为ODBC无法处理这些值。

The following table shows the format of the “zero” value for each type. The “zero” values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write. For temporal types that include a date part (DATE, DATETIME, and TIMESTAMP), use of these values may produce warning or errors. The precise behavior depends on which, if any, of the strict and NO_ZERO_DATE SQL modes are enabled; see Section 5.1.10, “Server SQL Modes”.

下表显示了每种类型的“零”值的格式。“零”值是特殊的，但是您可以使用表中显示的值显式地存储或引用它们。您还可以使用值'0'或0来实现这一点，这更容易编写。对于包含日期部分(date、DATETIME和TIMESTAMP)的时间类型，使用这些值可能会产生警告或错误。精确的行为取决于是否启用了strict和NO_ZERO_DATE SQL模式;参见5.1.10节“服务器SQL模式”。

Data Type	“Zero” Value
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000