MySQL Basics Tutorial 2 — Data Types: Date and Time Types
- 黄舟Original
- 2017-02-24 10:57:361235browse
The 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 '0000-00-00' to be saved as a "pseudo date" (if NO_ZERO_DATE SQL mode is not used). This is more convenient in some cases 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 each value you specify. An input value format (for example, 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 (for example, '98-09-04'), rather than month-day-year as is commonly used elsewhere. or day-month-year order (for example, '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 date or time type value 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 following table 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 |
· "Zero" values are special values, but you can save or reference them explicitly using the values 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 attributes since MySQL 4.1
DATETIME, DATE Related to the TIMESTAMP type. 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).
The 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:
· 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS 'Format string. "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.
· A string in the format of 'YYYY-MM-DD' or 'YY-MM-DD'. "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 without separators in the format of 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS', assuming that 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 that 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'.
The numeric 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 unqualified 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 a 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 of the DATE value No time information included.
· 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 the 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 are 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:
o Year values in the range of 00-69 are converted to 2000-2069.
o Annual values in the range of 70-99 are converted to 1970-1999.
1.1. TIMESTAMP attributes since MySQL 4.1
Note: In older versions of MySQL (prior to 4.1), the properties of the TIMESTAMP column type differed from this version in many ways. Very different from what is described in the section. If you need to convert old TIMESTAMP data to work in MySQL 5.1, see 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 have the server run in MAXDB mode for its connection as follows:
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 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 in tables that were 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 update 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 first 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 default value. The current timestamp and updated automatically.
o Does not use the DEFAULT or ON UPDATE clause, is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP.
o Use the DEFAULT CURRENT_TIMESTAMP clause instead of the ON UPDATE clause. The default value uses the current timestamp but does not update automatically.
o Without the DEFAULT clause but to use the ON UPDATE CURRENT_TIMESTAMP clause, the column has a default value of 0 and is automatically updated.
o 用常量DEFAULT值，列有给出的 默认值。如果列有一个ON UPDATE CURRENT_TIMESTAMP子句，它自动更新，否则不。
换句话说，你可以为初始值和自动更新的值使用当前的时间戳，或者其中一个使用，或者两个皆不使用。(例如，你可以指定ON UPDATE来启用自动更新而不让列自动初始化）。
· 在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或者NOW()。它们均具有相同的效果。
两个属性的顺序并不重要。如果一个TIMESTAMP列同时指定了DEFAULT和ON UPDATE，任何一个可以在另一个的前面。
例子，下面这些语句是等效的：
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);· 要为TIMESTAMP列而不是第1列指定自动默认或更新，必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如，DEFAULT 0或DEFAULT'2003-01-01 00:00:00')。然后，对于其它TIMESTAMP列，规则与第1个TIMESTAMP列相同，例外情况是不能忽略DEFAULT和ON UPDATE子句。如果这样做，则不会自动进行初始化或更新。
例如：下面这些语句是等效的：
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);可以对每个连接设置当前的时区。TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存一个TIMESTAMP值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。
可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如：
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 type
The YEAR type is a single-byte type used to represent the year.
MySQL retrieves and displays YEAR values in YYYY format. The range is 1901 to 2155.
You can specify YEAR values in various formats:
· Four-digit string, ranging from '1901' to '2155'.
· Four digits, ranging from 1901 to 2155.
· Two-digit string, ranging from '00' to '99'. Values in the range '00' to '69' and '70' to '99' are converted to YEAR values in the range 2000 to 2069 and 1970 to 1999.
· Two-digit integer in the range 1 to 99. Values in the range 1 to 69 and 70 to 99 are converted to YEAR values in the range 2001 to 2069 and 1970 to 1999. Note that two-digit integer ranges are slightly different from two-digit string ranges in that you cannot directly specify zero as a number and have it interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000.
· The result returned by the function has a value suitable for the YEAR context, such as NOW().
Illegal YEAR values are converted to 0000.
4. Y2K Matters and Date Types
MySQL itself is safe for the year 2000 (Y2K), but values entered into MySQL may not be safe. Any input containing a two-digit year value will be ambiguous because the century is not known. These values must be interpreted as four digits because MySQL uses four digits internally to store the year.
For DATETIME, DATE, TIMESTAMP, and YEAR types, MySQL uses the following rules to interpret dates with ambiguous year values:
· Year values in the range of 00-69 are converted to 2000-2069.
· Annual values in the range 70-99 are converted to 1970-1999.
Remember these rules are just reasonable guesses at what the data values represent. If the heuristics used by MySQL do not produce the correct value, you should provide the exact input containing the four-digit year value.
ORDER BY can correctly sort TIMESTAMP or YEAR values with two digits of 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)).
The above is the content of MySQL basic tutorial 2 - date and time types of data types. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!