DATE and time types representing time values are DATETIME, DATE, TIMESTAMP, TIME and YEAR. Each time type has a range of valid values and a "zero" value, which is used when specifying an illegal value that MySQL cannot represent. The TIMESTAMP type has proprietary automatic update features, which will be described later.
MySQL will give a warning or error if you try to insert an illegal date. You can use the ALLOW_INVALID_DATES SQL mode to make MySQL accept certain dates, such as '1999-11-31'. Useful when you want to save a "possibly wrong" value that the user has specified in the database (for example, in a web form) for future processing. In this mode, MySQL only verifies that the month range is from 0 to 12 and the day range is from 0 to 31. These ranges can include zero because MySQL allows saving day/month and dates where day is zero in a DATE or DATETIME column. This is useful when an application needs to save a birthday for which you don't know the exact date. In this case, just save the date as '1999-00-00' or '1999-01-00'. Functions that require a complete date such as DATE_SUB() or DATE_ADD will not give correct results if you save such a date. (If you don't want zeros to appear in dates, you can use NO_ZERO_IN_DATESQL mode).
MySQL also allows saving '0000-00-00' as a "pseudo date" (if NO_ZERO_DATE SQL mode is not used). This is more convenient in some situations than using NULL values (and the data and indexes take up less space).
Set the sql_mode system variable to the corresponding mode value to know more clearly what kind of dates you want MySQL to support.
The following points should be kept in mind when working with date and time types:
· MySQL retrieves values of a given date or time type in standard output format, but it does its best to interpret the various input value formats you specify (for example, when When you specify a value that is assigned to or compared to a date or time type). Only the formats described in the following sections are supported. You are expected to provide valid values. Unexpected results may occur if you use values in other formats.
· Dates containing two-digit year values are confusing because the century is not known. MySQL uses the following rules to interpret two-digit year values:
o Year values in the range 70-99 are converted to 1970-1999.
o The annual value in the range of 00-69 is converted to 2000-2069.
· Although MySQL attempts to interpret values in several formats, dates are always in year-month-day order (e.g., '98-09-04'), rather than month-day-year or day-month as is commonly used elsewhere. -Year order (eg, '09-04-98', '04-09-98').
· MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa.
· When MySQL encounters a value of a date or time type that is out of range or illegal for that type (as described at the beginning of this section), it converts the value to the "zero" value of that class. One exception is that out-of-range TIME values are clipped to the corresponding endpoint of the TIME range.
The table below shows the format of various "zero" values. Please note that using these values will generate warnings if NO_ZERO_DATE SQL mode is enabled.
Column type "zero" value
DATETIME '0000-00-00 00:00:00'
DATE '0000-00-00'
TIMESTAMP 00000000000000
TIME '00:00:00'
YEAR 0000
· The "zero" value is a special value, but you can save or reference them explicitly using the value shown in the table. You can also use the value '0' or 0 to save or reference, which is easier to write.
· "Zero" date or time values used in MyODBC are automatically converted to NULL in MyODBC 2.50.12 and above because ODBC cannot handle such values.
1. DATETIME, DATE and TIMESTAMP types
1. TIMESTAMP attribute since MySQL 4.1
DATETIME, DATE and TIMESTAMP types are related. This section describes their characteristics, their similarities and differences.
Use the DATETIME type when you need a value that contains both date and time information. 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'. ("Supported" means that while the previous value may work, there is no guarantee).
DATE type should be used when you only need the date value without the time part. MySQL uses 'YYYY-MM-DD' format to retrieve and display DATE values. The supported range is '1000-01-01' to '9999-12-31'.
The properties of the TIMESTAMP column type are not fixed and depend on the MySQL version and the SQL mode the server is running in. These properties are described later in this section.
DATETIME, DATE and TIMESTAMP values can be specified using any common format:
· Strings in the form 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. "Relaxed" syntax is allowed: any punctuation character can be used as a separator between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' and '98@12@31 11^30^45 ' are equivalent.
· String in 'YYYY-MM-DD' or 'YY-MM-DD' format. "Relaxed" syntax is also allowed here. For example, '98-12-31', '98.12.31', '98/12/31' and '98@12@31' are equivalent.
· A string in 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format without separators, assuming the string is meaningful for date types. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a meaningless minutes part) and will become '0000- 00-00 00:00:00'.
· A string in 'YYYYMMDD' or 'YYMMDD' format without separators, assuming the string is meaningful for date types. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has a meaningless month and day part) and will become '0000-00-00' '.
· A number in YYYYMMDDHHMMSS or YYMMDDHHMMSS format, assuming the number makes sense for the date type. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
· A number in YYYYMMDD or YYMMDD format, assuming the number makes sense for the date type. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
· The result returned by the function has a value suitable for DATETIME, DATE or TIMESTAMP context, such as NOW() or CURRENT_DATE.
Invalid DATETIME, DATE or TIMESTAMP values are converted to a "zero" value of the corresponding type ('0000-00-00 00:00:00', '0000-00-00' or 00000000000000).
For string values that include date part separators, if the day and month values are less than 10, there is no need to specify two digits. '1979-6-9' is the same as '1979-06-09'. Likewise, for string values that include time part separators, if the hours, minutes, and seconds values are less than 10, there is no need to specify two digits. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.
Number value should be 6, 8, 12 or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format, with the first 4 digits representing the year. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format, with the first 2 digits representing the year. Other numbers are interpreted as if zero-padded to the nearest length.
Values specified as non-qualifier strings are interpreted using the given length. If the string is 8 or 14 characters long, the first 4 digits represent the year. Otherwise, the first 2 digits represent the year. Interpret each component occurring within the string from left to right to discover the year, month, day, hour, minute, and second values. This means that strings shorter than 6 characters should not be used. For example, if you specify '9903' thinking it means March 1999, MySQL will insert a "zero" date value into your table. This is because the year and month values are 99 and 03, but the day part is completely missing, so the value is not a legal date. However, you can explicitly specify a zero value to represent the missing month or day part. For example, you can use '990300' to insert the value '1999-03-00'.
To a certain extent, it is possible to assign a value of one date type to a different date type. However, the value may change or lose some information:
· If you assign a DATE value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to '00:00:00' because the DATE value does not contain time information .
· If you assign a DATETIME or TIMESTAMP value to a DATE object, the time portion of the resulting value is removed because the DATE value does not contain time information.
· Remember that although DATETIME, DATE, and TIMESTAMP values can be specified using the same format, the ranges for different types of values are different. For example, TIMESTAMP value cannot be earlier than 1970 or later than 2037. This indicates that a date, such as '1968-01-01', while valid for DATETIME or DATE values, is not valid for TIMESTAMP values and will be converted to 0 if assigned to such an object.
Be aware of certain pitfalls when specifying date values:
· The non-strict format allowed for values specified as strings can be deceptive. For example, the value '10:11:12' may look like a time value due to the ':' separator, but if used in a date context value is interpreted as the year '2010-11-12'. The value '10:45:15' is converted to '0000-00-00' because '45' is not a legal month.
· In non-strict mode, the MySQL server only performs basic checks on the validity of dates: the ranges of year, month and day are 1000 to 9999, 00 to 12 and 00 to 31 respectively. Any date containing parts outside these ranges is converted to '0000-00-00'. Note that you are still allowed to save illegal dates, such as '2002-04-31'. To ensure that dates are valid when not using strict mode, you should check your application.
In strict mode, illegal dates are not accepted and not converted.
· Dates containing two-digit year values are confusing because the century is not known. MySQL uses the following rules to interpret two-digit year values: Year values in the range 00-69 are converted to 2000-2069.
o Annual values in the range of 70-99 are converted to 1970-1999.
1.1. TIMESTAMP attribute since MySQL 4.1
Note: In older versions of MySQL (prior to 4.1), the properties of the TIMESTAMP column type differed significantly in many ways from those described in this section. If you need to convert old TIMESTAMP data to work in MySQL 5.1, see the MySQL 4.1 Reference Manual for details.
The display format of the TIMESTAMP column is the same as the DATETIME column. In other words, the display width is fixed at 19 characters, and the format is YYYY-MM-DD HH:MM:SS.
MySQL server can also run in MAXDB mode. When the server is running in this mode, TIMESTAMP is equal to DATETIME. That is, if the server is running in MAXDB mode when the table is created, the TIMESTAMP column is created as a DATETIME column. The result is that the column uses the DATETIME display format, has the same value range, and is not automatically initialized or updated with the current date and time.
To enable MAXDB mode, use the --sql-mode=MAXDB server option when starting the server or set the SQL server mode to MAXDB at runtime by setting the global sql_mode variable:
mysql> SET GLOBAL sql_mode=MAXDB；
The client can enable the server as follows Run in MAXDB mode for its connections:
mysql> SET SESSION sql_mode=MAXDB;
MySQL does not accept timestamp values that include a zero in the day or month column or contain illegal date values. The only exception to this rule is the special value '0000-00-00 00:00:00'.
You can very flexibly determine when to initialize and update TIMESTAMP and which columns to initialize and update:
· You can specify the current timestamp as the default value and the automatically updated value. But you can only choose one, or neither. (It is not possible for one column to select one behavior and another column to select another behavior).
· You can specify which TIMESTAMP column is automatically initialized or updated to the current date and time. The 1st TIMESTAMP column is no longer needed.
Please note that the information discussed below only applies to TIMESTAMP columns of tables created without MAXDB mode enabled. (As mentioned above, MAXDB mode causes columns to be created as DATETIME columns). The rules that control the initialization and updating of TIMESTAMP columns are as follows:
· If the first TIMESTAMP column in a table is specified as a DEFAULT value, it cannot be ignored. The default value can be CURRENT_TIMESTAMP or a constant date and time value.
· DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP of the 1st TIMESTAMP column. For other TIMESTAMP columns, DEFAULT NULL is treated as DEFAULT 0.
· Any TIMESTAMP column in the table can be set to automatically initialize to the current timestamp and/or update.
· In the CREATE TABLE statement, you can declare the first TIMESTAMP column in any of the following ways:
o Use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses to use the current timestamp as the default value and automatically update .
o Does not use DEFAULT or ON UPDATE clause, same as DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP.
o Use the DEFAULT CURRENT_TIMESTAMP clause instead of the ON UPDATE clause. The column uses the current timestamp as the default value but does not update automatically.
o Without the DEFAULT clause but with the ON UPDATE CURRENT_TIMESTAMP clause, the column has a default value of 0 and is automatically updated.
o Use the constant DEFAULT value, and the default value given is listed. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it updates automatically, otherwise it does not.
In other words, you can use the current timestamp for the initial value and the auto-updated value, or one of them, or neither. (For example, you can specify ON UPDATE to enable automatic updates without having the columns automatically initialized).
· You can use CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() or NOW() in the DEFAULT and ON UPDATE clauses. They all have the same effect.
The order of the two properties is not important. If both DEFAULT and ON UPDATE are specified for a TIMESTAMP column, either can precede the other.
Example, the following statements are equivalent:
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);· To specify automatic default or update for a TIMESTAMP column other than column 1, you must disable automatic initialization by explicitly assigning the 1st TIMESTAMP column a constant DEFAULT value and updates. (For example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for other TIMESTAMP columns, the rules are the same as for the first TIMESTAMP column, with the exception that the DEFAULT and ON UPDATE clauses cannot be ignored. If you do this, no initialization or update will occur automatically.
For example: The following statements are equivalent:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);The current time zone can be set for each connection. TIMESTAMP values are saved in UTC format, converted to the current time zone when stored, and converted back to the current time zone when retrieved. As long as the time zone setting value is a constant, the value at the time of saving can be obtained. If you save a TIMESTAMP value, you should change the time zone and then retrieve the value, it will be different from the value you saved. This is because the same time zone is not used in the conversion in both directions. The current time zone can be used as the value of the time_zone system variable.
You can include the NULL attribute in the definition of a TIMESTAMP column to allow the column to contain NULL values. For example:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
如果未指定NULL属性，将列设置为NULL设置则会将它设置为当前的时间戳。请注意允许NULL值的TIMESTAMP列不会采用当前的时间戳，除非要么其 默认值定义为CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到该列内。换句话说，只有使用如下定义创建，定义为 NULL的TIMESTAMP列才会自动更新：
CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)；
否则-也就是说，如果使用NULL而不是DEFAULT TIMESTAMP来定义TIMESTAMP列，如下所示...
CREATE TABLE t1 (ts NULL DEFAULT NULL); CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
...则必须显式插入一个对应当前日期和时间的值。例如：
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
2. TIME类型
MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。
你可以用各种格式指定TIME值：
· 'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法：'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0到34之间的值。请注意MySQL还不保存分数。
· 'HHMMSS'格式的没有间割符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，将变为'00:00:00'。
· HHMMSS格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。下面格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。
· 函数返回的结果，其值适合TIME上下文，例如CURRENT_TIME。
对于指定为包括时间部分间割符的字符串的TIME值，如果时、分或者秒值小于10，则不需要指定两位数。'8:3:2'与'08:03:02'相同。
为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12 被解释为 '00:00:12'。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。
超出TIME范围但合法的值被裁为范围最接近的端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。
无效TIME值被转换为'00:00:00'。请注意由于'00:00:00'本身是一个合法TIME值，只从表内保存的一个'00:00:00'值还不能说出原来的值是 '00:00:00'还是不合法的值。
3. YEAR类型
YEAR类型是一个单字节类型用于表示年。
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
可以指定各种格式的YEAR值：
· 四位字符串，范围为'1901'到'2155'。
· 四位数字，范围为1901到2155。
· 两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。
· 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。
· 函数返回的结果，其值适合YEAR上下文，例如NOW()。
非法YEAR值被转换为0000。
4. Y2K事宜和日期类型
MySQL本身对于2000年(Y2K)是安全的，但输入给MySQL的值可能不安全。任何包含两位年值的输入都会令人模糊，因为世纪不知道。这些值必须解释为四位形式，因为MySQL内部使用四位来保存年。
对于DATETIME、DATE、TIMESTAMP和YEAR类型，MySQL使用以下规则解释含模糊年值的日期：
· 00-69范围的年值转换为2000-2069。
· 70-99范围的年值转换为1970-1999。
请记住这些规则只是合理猜测数据值表示什么。如果MySQL使用的启发不能产生正确的值，你应提供包含四位年值的确切输入。
ORDER BY可以正确排序有两位年的TIMESTAMP或YEAR值。
Some functions such as MIN() and MAX() convert TIMESTAMP or YEAR into a number. This means that these functions do not work correctly with values that have two-digit year values. The fix in this case is to convert TIMESTAMP or YEAR to four-digit year format or use MIN(DATE_ADD(TIMESTAMP,INTERVAL 0 DAYS)).
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
