MySQL 数据类型：日期和时间数据类型

关于：日期和时间数据类型

表示时间值的日期和时间数据类型为DATE，TIME，DATETIME，TIMESTAMP和YEAR。

TIMESTAMP 和 DATETIME 类型具有特殊的自动更新行为。

每个时间类型都有一个有效值范围，以及一个“零”值：

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

对于包含日期部分（DATE，DATETIME 和 TIMESTAMP）的时间类型，使用这些值可能会产生警告或错误。

注意事项：

MySQL 以标准输出格式检索给定日期或时间类型的值，但 MySQL 尝试解释您提供的输入值的多种格式（例如，当您指定要分配给日期或时间的值或与之比较时）。必须提供有效的值，否则可能会发生不可预测的结果。
尽管 MySQL 尝试以多种格式解释值，但日期部分必须始终以“年月日”顺序给出（例如'98-09-04'），而不是以其他地方通常使用的“月日年”或“日月年”。要将其他顺序的字符串转换为“年-月-日”，可以使用“STR_TO_DATE()”函数。
包含两位数年份值的日期是不明确的，因为世纪是未知的。 MySQL 使用以下规则解释两位数的年份值：
1. “70-99”范围内的年份值变为“1970-1999”。
2. “00-69”范围内的年份值变为“2000-2069”。
如果在数字上下文中使用日期或时间值，则 MySQL 自动将日期或时间值转换为数字，反之亦然。
默认情况下，当 MySQL 遇到日期或时间类型的值超出范围或对该类型无效时，它将将该值转换为该类型的“零”值。例外情况是超出范围的“TIME”值将被裁剪到“TIME”范围的适当端点。

通过将 SQL 模式设置为适当的值，可以更准确地指定希望 MySQL 支持的日期类型。通过启用“ALLOW_INVALID_DATES” SQL 模式，可以使 MySQL 接受某些日期，例如“2009-11-31”。当您要在数据库中存储用户指定(例如，以 Web 形式指定)的“可能错误”的值以供将来处理时，此功能很有用。在这种模式下，MySQL 仅验证“月份”在 1 到 12 的范围内，并且“日期”在 1 到 31 的范围内。
MySQL 允许您在“DATE”或“DATETIME”列中存储日期或星期为零的日期。【这对于需要存储您可能不知道确切日期的生日的应用程序很有用】在这种情况下，您只需将日期存储为'2009-00-00'或'2009-01-00'即可。但是，对于这样的日期，那些需要完整日期的函数（例如“DATE_SUB()”或“DATE_ADD()”）将不能获得正确的结果。要禁止日期中的为零的月或日的部分，请启用“NO_ZERO_IN_DATE”模式。
MySQL 允许您将'0000-00-00'的“零”值存储为“虚拟日期”。在某些情况下，这比使用 NULL 值更方便，并且使用较少的数据和索引空间。要禁止'0000-00-00'，请启用“NO_ZERO_DATE”模式。
通过连接器/ODBC 使用的“零”日期或时间值会自动转换为“NULL”，因为 ODBC 无法处理此类值。

DATE，DATETIME 和 TIMESTAMP 类型

DATE，DATETIME和TIMESTAMP类型是相关的。

DATE 类型用于具有日期部分但没有时间部分的值。 MySQL 检索并以'YYYY-MM-DD'格式显示DATE值。支持的范围是'1000-01-01'到'9999-12-31'。
DATETIME 类型用于包含日期和时间部分的值。 MySQL 检索并以'YYYY-MM-DD hh:mm:ss'格式显示DATETIME值。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
TIMESTAMP 数据类型用于包含日期和时间部分的值。 TIMESTAMP的范围是'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。

DATETIME 或 TIMESTAMP 值可以包含尾随的小数秒部分，精度最高为微秒(6 位数)。特别是，存储在 DATETIME 或 TIMESTAMP 列中的值中的任何小数部分都将被存储而不是被丢弃。包括小数部分，这些值的格式为'YYYY-MM-DD hh:mm:ss[.fraction]'，DATETIME值的范围为'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'，并且TIMESTAMP值的范围为'1970-01-01 00:00:01.000000'到'2038-01-19 03:14:07.999999'。小数部分应始终与其余时间用小数点分隔；没有其他小数秒分隔符被识别。

TIMESTAMP 和 DATETIME 数据类型提供自动初始化并更新到当前日期和时间。

MySQL 将 TIMESTAMP 值从当前时区转换为 UTC 进行存储，然后从 UTC 转换回当前时区进行检索。（对于其他类型，例如DATETIME，不会发生这种情况）默认情况下，每个连接的当前时区是服务器的时间。可以在每个连接的基础上设置时区。只要时区设置保持不变，您将获得与存储相同的值。如果存储 TIMESTAMP 值，然后更改时区并检索该值，则检索到的值与您存储的值不同。发生这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可用作 time_zone 系统变量的值。

如果 SQL 模式允许此转换，则无效的 DATE，DATETIME 或 TIMESTAMP 值将转换为适当类型('0000-00-00'或'0000-00-00 00:00:00')的“零”值。精确的行为取决于是否启用了严格 SQL 模式和 NO_ZERO_DATE SQL 模式中的哪一个；

TIME 类型

MySQL 检索并以'hh:mm:ss' 格式（或 'hhh:mm:ss' 格式表示大小时数）显示一个 TIME 值。 TIME值的范围可以从'-838:59:59'到'838:59:59'【用于表示时间间隔】。小时部分可能会很大，因为TIME类型不仅可以表示一天中的某个时间（必须少于 24 小时），而且还可以表示经过的时间或两个事件之间的时间间隔（可能比 24 小时大，甚至是负面的）。

MySQL 可以多种格式识别TIME值，其中某些可以包含尾随小数秒的部分，精度最高为微秒(6 位数)。包括小数部分，TIME值的范围是'-838:59:59.000000'至'838:59:59.000000'。

将缩写值分配给 TIME 列时要小心
1. MySQL 将带冒号的 TIME 缩写值解释为一天中的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。
2. MySQL 使用最右边的两个数字表示秒（即经过时间而不是一天中的时间）来解释不带冒号的缩写值。例如，您可能认为'1112'和'1112'的意思是'11:12:00'(11 点后 12 分钟)，但是 MySQL 将它们解释为'00:11:12'(11 分钟 12 秒)。类似地，'12'和12被解释为'00:00:12'。
在时间部分和小数秒部分之间唯一识别的分隔符是小数点。
默认情况下，位于 TIME 范围之外但有效的值将被裁剪到该范围的最近端点。例如，'-850:00:00'和'850:00:00'转换为'-838:59:59'和'838:59:59'。无效的TIME值将转换为'00:00:00'。请注意，由于'00:00:00'本身是有效的TIME值，因此无法从存储在 table 中的'00:00:00'值中判断原始值是被指定为'00:00:00'还是无效。

年类型

YEAR 类型是 1 字节类型，用于表示年份。可以将其声明为 YEAR，隐式显示宽度为 4 个字符，或者等效为YEAR(4)，以显式显示宽度。

不建议使用两位数的 YEAR(2) 数据类型，并在 MySQL 5.7.5 中删除了对它的支持。要将 2 位数YEAR(2)列转换为 4 位数YEAR列。【包含两位数年份值的日期是不明确的，因为世纪是未知的】

MySQL 以“YYYY”格式显示YEAR值，范围为 1901 到 2155 和 0000。

YEAR接受多种格式的输入值：

作为'1901'到'2155'范围内的 4 位数字字符串。
为1901到2155范围内的 4 位数字。
作为'0'到'99'范围内的 1 或 2 位数字字符串。 MySQL 会将'0'到'69'范围内的值和'70'到'99'到YEAR范围内的值转换为2000到2069以及1970到1999范围内的值。
以0到99范围内的 1 或 2 位数字表示。 MySQL 会将1到69范围内的值和70到99到YEAR范围内的值转换为2001到2069以及1970到1999范围内的值。

插入数字 0 的结果的显示值为 0000，内部值为 0000。要插入零并将其解释为 2000，请将其指定为字符串'0'或'00'。
在 YEAR 上下文中，将函数的返回值为数值是可以接受的，例如“NOW()”。
如果未启用严格的 SQL 模式，则 MySQL 会将无效的YEAR值转换为0000。在严格的 SQL 模式下，尝试插入无效的YEAR值会产生错误。

YEAR(2) 的缺陷，以及迁移到 YEAR(4)

因为“YEAR(2)”显示的值仅指示内部值的最后两位数字并省略世纪数字。在某些情况下【在转储和重新加载，或将值转换为字符串时】，可能会造成信息丢失。因此，应该在需要使用年值数据类型的地方使用“YEAR”或“YEAR(4)”。

从 MySQL 5.7.5 开始，不再支持 YEAR(2)，必须将现有的 YEAR(2) 列转换为 4 位YEAR列才能再次使用。

YEAR(2) 的缺陷示例：

mysql> CREATE TABLE t (y2 YEAR(2), y4 YEAR);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO t (y2) VALUES(1912),(2012),(2112);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t SET y4 = y2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM t;
+------+------+
| y2   | y4   |
+------+------+
|   12 | 1912 |
|   12 | 2012 |
|   12 | 2112 |
+------+------+
3 rows in set (0.00 sec)

可能造成的情况：

如果使用 mysqldump 转储在上一个示例中创建的 table，则转储文件使用相同的 2 位数字表示形式(12)表示所有y2值。如果从转储文件中重新加载 table，则所有结果行的内部值均为2012，显示值为12，因此将失去它们之间的区别。
将 2 位数或 4 位数的 YEAR 数据值转换为字符串形式将使用数据类型显示宽度。假设 YEAR(2) 列和 YEAR / YEAR(4) 列都包含值1970。将每一列分配给一个字符串将分别导致'70'或'1970'的值。即，从YEAR(2)转换为字符串会发生信息丢失。
当将1970到2069范围之外的值插入到CSV表的YEAR(2)列中时，存储不正确。例如，插入2211会导致显示值11但内部值2011。

所以，从 MySQL 5.7.5 开始，不再支持YEAR(2)：

从 MySQL 5.7.5 开始，新 table 的YEAR(2)列定义产生“ER_INVALID_YEAR_COLUMN_LENGTH”错误：
现有 table 中的YEAR(2)列仍为YEAR(2)：
从 MySQL 5.7.5 开始，查询中的YEAR(2)列会产生警告或错误。

几个程序或语句自动将YEAR(2)列转换为 4 位YEAR列：

ALTER TABLE导致 table 重建的语句。
REPAIR TABLE（如果它找到包含YEAR(2)列的 table，则建议您使用CHECK TABLE）。
mysql_upgrade(使用REPAIR TABLE)。
使用mysqldump转储并重新加载转储文件。与前三个项目执行的转换不同，转储和重新加载有可能更改数据值。

关于迁移到 YEAR / YEAR(4)：

要将 2 位数的YEAR(2)列转换为 4 位数的YEAR列，您可以随时手动进行，而无需升级。
另外，您可以升级 MySQL 版本，减少或删除对 YEAR(2) 的支持（MySQL 5.6.6 或更高版本），然后让 MySQL 自动转换YEAR(2)列。【这种情况下，请避免通过转储和重新加载数据进行升级，因为这可能会更改数据值。】

另外，如果使用复制，则必须考虑升级注意事项。

要将 2 位数的YEAR(2)列手动转换为 4 位数的YEAR，请使用“ALTER TABLE”或“REPAIR TABLE”。假设 tablet1具有以下定义：

CREATE TABLE t1 (ycol YEAR(2) NOT NULL DEFAULT '70');

使用ALTER TABLE修改列，如下所示：

ALTER TABLE t1 FORCE;

ALTER TABLE语句在不更改YEAR(2)值的情况下转换 table。

如果服务器是复制主服务器，则“ALTER TABLE”语句复制到从属服务器，并在每个服务器上更改相应的 table。

另一种迁移方法是执行二进制升级：在不转储和重新加载数据的情况下就地升级 MySQL。然后运行“mysql_upgrade”，它使用“REPAIR TABLE”将 YEAR(2) 列转换为 4 位YEAR列而不更改数据值。

如果服务器是复制主服务器，则“REPAIR TABLE”语句复制到从属服务器，并在每个服务器上进行相应的 table 更改，除非您使用“--skip-write-binlog”选项调用“mysql_upgrade”。

升级到复制服务器通常需要将从服务器升级到较新版本的 MySQL，然后再升级主服务器。例如，如果主服务器和从服务器都运行 MySQL 5.5，则典型的升级过程包括将从服务器升级到 5.6，然后再将主服务器升级到 5.6. 关于从 MySQL 5.6.6 开始对 YEAR(2) 的不同处理，该升级序列会导致一个问题：假设从属服务器已被升级，但主服务器尚未被升级。然后，在主服务器上创建一个包含 2 位数YEAR(2)列的 table，将在从服务器上创建一个包含 4 位数YEAR列的 table。因此，如果您使用基于语句的复制，则以下操作在主服务器和从服务器上的结果将有所不同：

插入数字 0。结果值在主机上的内部值为2000，在从机上的内部值为0000。
将YEAR(2)转换为字符串。此操作在主机上使用YEAR(2)的显示值，在从机上使用YEAR(4)的显示值。

为避免此类问题，请在升级之前将主文件上的所有 2 位YEAR(2)列修改为 4 位YEAR列。（如前所述，使用“ALTER TABLE”）这样就可以正常升级（先从动，然后再升级为主），而无需在主从之间引入YEAR(2)到YEAR(4)的差异。

应避免一种迁移方法：请勿使用mysqldump转储数据，并在升级后重新加载转储文件。如前所述，这可能会更改YEAR(2)值。

从 2 位YEAR(2)列到 4 位YEAR列的迁移还应包括检查应用程序代码在以下情况下是否有可能更改行为：

期望选择 YEAR 列以产生正好两位数的代码。
不考虑数字 0 插入的不同处理的代码：将 0 插入YEAR(2)或YEAR(4)分别导致内部值2000或0000。

TIMESTAMP 和 DATETIME 的自动初始化和更新

TIMESTAMP 和 DATETIME 列可以自动初始化并更新为当前日期和时间（即当前时间戳）。

对于 table 中的任何 TIMESTAMP 或 DATETIME 列，您都可以将当前时间戳记指定为默认值和/或自动更新值：

对于未为该列指定值的插入行，将自动初始化的列设置为当前时间戳。
当行中任何其他列的值从其当前值更改时，自动更新的列将自动更新为当前时间戳。
如果将所有其他列设置为其当前值，则自动更新的列将保持不变。

为防止自动更新的列在其他列更改时更新，请将其显式设置为其当前值。

要即使其他列未更改也要更新自动更新的列，请将其显式设置为它应具有的值（例如，将其设置为CURRENT_TIMESTAMP）。

此外，如果禁用了explicit_defaults_for_timestamp系统变量，则可以通过为其分配 NULL 值来初始化或更新任何 TIMESTAMP（但不是DATETIME）列到当前日期和时间，除非已使用 NULL 属性定义了该值以允许 NULL 值。【？？？】

要指定自动属性，请在列定义中使用DEFAULT CURRENT_TIMESTAMP（默认当前值）和ON UPDATE CURRENT_TIMESTAMP（自动更新到当前值）子句。

如果列定义中同时存在这两者，则任何一个都可以首先出现。（顺序无影响）
CURRENT_TIMESTAMP 的同义词：“CURRENT_TIMESTAMP()”，“NOW()”，“LOCALTIME”，“LOCALTIME()”，“LOCALTIMESTAMP”和“LOCALTIMESTAMP()”。（与 CURRENT_TIMESTAMP 的含义相同）
DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 的使用特定于 TIMESTAMP 和 DATETIME 。
DEFAULT 子句还可以用于指定常量（非自动）默认值（例如“DEFAULT 0”或“DEFAULT '2000-01-01 00:00:00'”）。

示例：

默认值为当前时间戳，并自动更新为当前时间戳：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

默认值为当前时间戳，但不自动更新为当前时间戳：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

默认值为给定值（常数），但不自动更新为当前时间戳：
```
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);
```

默认值为给定值（常数），并自动更新为当前时间戳：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

不指定默认值，但自动更新为当前时间戳：【在这种情况下，默认值取决于类型】

除非使用“NULL”属性定义，否则TIMESTAMP的默认值为 0：

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

除非使用“NOT NULL”属性定义，否则DATETIME的默认值为 NULL：

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

除非明确指定了 TIMESTAMP 和 DATETIME 列，否则它们没有自动属性，但以下情况除外：如果禁用“explicit_defaults_for_timestamp”系统变量，则“第一个” TIMESTAMP 列同时具有“DEFAULT CURRENT_TIMESTAMP”和“ON UPDATE CURRENT_TIMESTAMP”（如果未明确指定）。要取消对第一个 TIMESTAMP 列的自动属性，请使用以下策略之一：

启用“explicit_defaults_for_timestamp”系统变量。在这种情况下，指定自动初始化和更新的“DEFAULT CURRENT_TIMESTAMP”和“ON UPDATE CURRENT_TIMESTAMP”子句是可用的，但是除非列定义中明确包含，否则它们不会分配给任何 TIMESTAMP 列。
或者，如果禁用了“explicit_defaults_for_timestamp”，请执行以下任一操作：
1. 使用“DEFAULT”子句定义该列，该子句指定一个恒定的默认值。
2. 指定“NULL”属性。这也会导致该列允许 NULL 值，这意味着您无法通过将列设置为 NULL 来分配当前时间戳。分配 NULL 会将列设置为 NULL，而不是当前时间戳。要分配当前时间戳，请将列设置为“CURRENT_TIMESTAMP”或诸如“NOW()”的同义词。

如下 table 定义：（禁用了“explicit_defaults_for_timestamp”）

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
  ts1 TIMESTAMP NULL,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
  ts1 TIMESTAMP NULL DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

其中：

在每个 table 定义中，第一 TIMESTAMP 列没有自动初始化或更新。
这些 table 在ts1列处理NULL值的方式上有所不同。对于t1，ts1是NOT NULL，为其分配值NULL会将其设置为当前时间戳。对于t2和t3，ts1允许NULL并为其分配值NULL将其设置为NULL。
t2和t3的默认值ts1有所不同。对于t2，将ts1定义为允许NULL，因此在没有显式DEFAULT子句的情况下，默认值也是NULL。对于t3，ts1允许NULL但显式默认值为 0。

如果 TIMESTAMP 或 DATETIME 列定义在任何地方都包含显式的小数秒精度值，则在整个列定义中必须使用相同的值。

这是允许的：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

这是不允许的：

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

TIMESTAMP 初始化和 NULL 属性：

禁用“explicit_defaults_for_timestamp”：
1. 默认情况下 TIMESTAMP 列为“NOT NULL”，不能包含NULL值，并且分配 NULL 会分配当前时间戳。
2. 使用“NULL”属性显式声明TIMESTAMP，可以包含NULL值，分配 NULL 既为 NULL 而非当前时间戳。（默认值也将变为NULL，除非被指定不同默认值的 DEFAULT 子句覆盖）
启用“explicit_defaults_for_timestamp”：
1. 无论使用“NULL”还是“NOT NULL”属性声明，都不能分配当前时间戳，必须使用“CURRENT_TIMESTAMP”（或诸如“NOW()”之类的同义词）。
2. 使用“NULL”属性声明时，TIMESTAMP 列才允许 NULL 值。

时间值的分数秒

MySQL 支持TIME，DATETIME和TIMESTAMP值的小数秒，精度高达微秒(6 位)：

定义包含小数秒部分的列，使用语法“type_name(fsp)”，其中“type_name”是“TIME”，“DATETIME”或“TIMESTAMP”，而“fsp”是小数秒精度。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

“fsp”的值(如果给定)必须在 0 到 6 的范围内。0 表示没有小数部分。如果省略，则默认精度为 0。(与标准 SQL 默认值 6 不同，以便与以前的 MySQL 版本兼容)

将具有小数秒部分的 TIME，DATE 或 TIMESTAMP 值插入相同类型但小数位数较少的列中会导致舍入。考虑如下创建和填充的 table：

CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

时间值通过四舍五入插入 table 中：

mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+

舍入时不发出警告或错误。

带有时间参数的函数接受小数秒后的值。时间函数的返回值包括适当的小数秒。例如，不带参数的“NOW()”返回不带小数部分的当前日期和时间，但采用 0 到 6 之间的可选参数来指定返回值包括该位数的小数秒部分。
时间 Literals 的语法产生时间值：DATE 'str'，TIME 'str'和TIMESTAMP 'str'，以及 ODBC 语法等效项。如果指定，结果值将包含尾随的小数秒部分。以前，暂时类型关键字被忽略，这些构造产生了字符串值。【？？？】

日期和时间类型之间的转换

在某种程度上，您可以将值从一种时间类型转换为另一种时间类型。但是，价值可能会有所变化或信息丢失。在所有情况下，时间类型之间的转换都取决于结果类型的有效值范围。

例如，尽管都可以使用相同的格式集来指定“DATE”，“DATETIME”和“TIMESTAMP”值，但是这些类型的值范围都不相同。“TIMESTAMP”值不能早于1970 UTC 或晚于'2038-01-19 03:14:07' UTC。这意味着诸如'1968-01-01'之类的日期虽然作为“DATE”或“DATETIME”值有效，但作为TIMESTAMP值无效，并转换为0。

“DATE”值的转换：

转换为“DATETIME”或“TIMESTAMP”值会添加'00:00:00'的时间部分，因为“DATE”值不包含任何时间信息。
转换为“TIME”值没有用；结果是'00:00:00'。

“DATETIME”或“TIMESTAMP”值的转换：

转换为“DATE”值需要考虑小数秒，并舍入时间部分。例如，'1999-12-31 23:59:59.499'变为'1999-12-31'，而'1999-12-31 23:59:59.500'变为'2000-01-01'。
转换为“TIME”值会丢弃日期部分，因为TIME类型不包含日期信息。

“TIME”值的转换：为了将“TIME”值转换为其他时间类型，将“CURRENT_DATE()”的值用作日期部分。“TIME”被解释为经过的时间（而非一天中的时间），并添加到日期中。这意味着，如果时间值超出 '00:00:00' 到 '23:59:59' 的范围，则结果的日期部分与当前日期不同。

假设当前日期为'2012-01-01'。则 '12:00:00'，'24:00:00'和'-12:00:00'的“TIME”值转换为“DATETIME”或“TIMESTAMP”值时，分别产生'2012-01-01 12:00:00'，'2012-01-02 00:00:00'和'2011-12-31 12:00:00'。

“TIME”到“DATE”的转换是类似的，但是从结果中丢弃了时间部分：分别为'2012-01-01'，'2012-01-02'和'2011-12-31'。

显式转换可用于覆盖隐式转换。例如：

在比较“DATE”和“DATETIME”值时，通过添加'00:00:00'的时间部分，将“DATE”值强制为“DATETIME”类型。
要通过忽略“DATETIME”值的时间部分来执行比较，请按以下方式使用“CAST()”函数：
date_col = CAST(datetime_col AS DATE)

将“TIME”和“DATETIME”值转换为数字形式（例如，通过添加“+0”）取决于该值是否包含小数秒部分：

当“N”为 0(或省略)时，“TIME(N)”或“DATETIME(N)”转换为整数；
当“N”大于 0 时，“TIME(N)”或“DATETIME(N)”将转换为带有“N”十进制数字的“DECIMAL”值：

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+

MySQL 数据类型：日期和时间数据类型

目录

关于：日期和时间数据类型

DATE，DATETIME 和 TIMESTAMP 类型

TIME 类型

年类型

YEAR(2) 的缺陷，以及迁移到 YEAR(4)

YEAR(2) 的缺陷示例：

关于迁移到 YEAR / YEAR(4)：

TIMESTAMP 和 DATETIME 的自动初始化和更新

时间值的分数秒

日期和时间类型之间的转换

导航菜单

MySQL 数据类型：日期和时间数据类型

关于：日期和时间数据类型

DATE，DATETIME 和 TIMESTAMP 类型

TIME 类型

年类型

YEAR(2) 的缺陷，以及迁移到 YEAR(4)

YEAR(2) 的缺陷示例：

关于迁移到 YEAR / YEAR(4)：

TIMESTAMP 和 DATETIME 的自动初始化和更新

时间值的分数秒

日期和时间类型之间的转换

导航菜单

搜索