傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 ) - MySQL 拾遗

在我十年的 IT 生涯中，好像几乎没有使用过 MySQL 的 date 、datetime 和 timestamp 几种数据类型。

究其原因，有两个：一是傻傻的分不清 DATE、DATETIME 和 TIMESTAMP 三种数据类型，记不住它们的格式；二是，它们或多或少和时区相关，为了存储时区无关数据，我一般都使用 int(11) 存储时间戳格式

今天就称这个机会，我们一起来熟悉下这三种数据类型

DATE、DATETIME 和 TIMESTAMP 的格式

首先要说明的是，这三种数据类型是息息相关的，它们有很多相似之处，也有各自的特征，因为这些特征，才导致了彼此的不同。

其次，MySQL 可以识别多种格式的 date、datetime 和 timestamp ，具体支持的格式有

对于 date 类型，支持 YYYY-MM-DD 或 YY-MM-DD 以及它们各自的变形

说到变形，就是指中划线 ( - ) 可以替换为任意字符，除了数字 0-9 ，比如

格式范例

YYYY-MM-DD 2018-09-13

YY-MM-DD 18-09-13

YYYY/MM/DD 2018/09/13

YY/MM/DD 18/09/13

YYYY^MM^DD 2018^09^13

YY^MM^DD 18^09^13

YYYY@MM@DD 2018@09@13

YY@MM@DD 18@09@13

同时还支持 YYYYMMDD 和 YYMMDD 的字符串形式，例如 '20180913' 和 '180913'。

还支持 YYYYMMMDD 和 YYMMDD 的数字格式，例如 20180913 和 180913。

格式	范例
YYYY-MM-DD	2018-09-13
YY-MM-DD	18-09-13
YYYY/MM/DD	2018/09/13
YY/MM/DD	18/09/13
YYYY^MM^DD	2018^09^13
YY^MM^DD	18^09^13
YYYY@MM@DD	2018@09@13
YY@MM@DD	18@09@13

对于 datetime 和 timestamp 类型，支持 YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS，一起它们的变形

这个变形其实和 DATE 一样，而且就是指中划线 ( - ) 可以替换为任意字符，除了数字 0-9 。

更进一步，这两个类型可以针对日期部分和时间部分使用不同的分隔符

格式	范例
YYYY-MM-DD HH:MM:SS	2018-09-13 21:15:10
YY-MM-DD HH:MM:SS	18-09-13 21:15:10
YYYYY^MM^DD HH+MM+SS	2018^09^13 21+15+10
YY^MM^DD HH+MM+SS	18^09^13 21+15+10
YYYY^MM^DD	2018^09^13
YY^MM^DD	18^09^13
YYYY@MM@DD HH^MM^SS	2018@09@13 21^15^10
YY@MM@DD HH^MM^SS	18@09@13 21^15^10

这种格式下也有几个点要注意

日期和时间部分与小数秒部分之间的唯一可用的分隔符号是小数点 ( . ) 例如 2018-09-13 21:15:10.11
日期与时间部分的分隔符不一定就要使用空格 ( ' ' )，还可以使用字符 T ，例如 2018-09-13 21:15:10 和 2018-09-13T21:15:10 是等价的

同时还可以使用没有任何分隔符的形式，比如 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS，例如 '20180913211510' 与 2018-09-13 21:15:10 是等价的

但 071122129015 则是非法的，并不是因为年份缺少了，而是因为分钟太大 ( 90 )，然后会被视为 0000-00-00 00:00:00

因为 MySQL 同时支持超大整数类型，所以，数字形式的 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 也是被支持的，例如 20180913211510 和 180913211510 被认为与 2018-09-13 21:15:10 是等价的

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

和 datetime 类型一样，timestamp 类型通常也包含完整的日期时间，也能够完整的表达某一个时刻。MySQL 也以 YYYY-MM-DD HH:MM:SS 格式检索并显示 timestamp 类型的值，但是，timestamp 支持的范围是不同的，只能是1970-01-01 00:00:00 UTC 到 2038-12-31 23:59:59 UTC

也就是说，支持的格式和显示上，datetime 和 timestamp 并没有什么不同，但在支持的范围上是有不同的

DATETIME 和 TIMESTAMP 的小数部分

datetime 和 timestamp 两种时间类型还可以精确到微秒，具体的形式就是支持在秒后面使用 6 位精度的小数来支持，例如 2018-09-13 21:15:10.111111。

而且，插入 datetime 或 timestamp 列的值中的任何小数部分都将被存储而不是被丢弃。

当包含小数部分时，datetime 和 timstamp 两个日期时间类型的格式为 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

注意：小数部分与其它部分的唯一的分隔符只能是小数点 ( . )，且没有任何其它可替代的字符。

DATETIME 和 TIMESTAMP 支持自动赋值

MySQL 还为 datetime 和 timestamp 两种日期时间类型提供了自动赋值功能。也就是在 MySQL INSERT 时可以自动初始化为当前日期时间，在 MySQL Update 时自动更新为当前时间。

而具体的做法，就是创建表结构时为这两种日期时间类型添加以下约束：

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

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 insert 操作时自动插入当前日期时间
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 update 操作时自动更新为当前日期时间

DATETIME 和 TIMESTAMP 的时区问题

datetime 和 timestamp 两种类型的另一个区别，就是它们对待时区有所不同

timestamp 会将值的时区从当前时区转换为 UTC 然后存储，并在需要显示和检索时从 UTC 转换回当前时区
但对于 datetime 类型，什么都不会发生，值是什么时区，存储的就是什么时区

默认情况下，timestamp 和 datetime 都会将当前连接的 MySQL 服务器的时区当作当前时区，当然了，这个时区是可配置的，而且可以针对每个连接单独配置。

从某些方面说，在数据转移或者在不同地方显示时，只要设置了一样的时区，那么数据就是一致的，否额

datetime 的值虽然存储和显示的时候都是同一个值，但可能不是我们想要的，因为时区错了
timestamp 虽然可以保证时间是正常的，但存储的值和显示的值可能会不一样，可能会导致我们错觉发生

PS 就是因为这个原因，导致了我不敢轻易使用 DATETIME 数据类型，连带 timestamp 也不敢使用

DATE、DATETIME 和 TIMESTAMP 的注意事项

对于这三种日期时间类型，无效的值将会转换为相应的零值，也就是，date 类型会转换为 0000-00-00 ，datetime 类型会转换为 0000-00-00 00:00:00 ，而 timestamp 则会转换为 1970-01-01 00:00:00。
虽然 MySQL 支持为指定为字符串的值使用宽松格式，其中任何标点字符都可以用作日期部分或时间部分之间的分隔符，但是，在某些情况下，这种语法可能是欺骗性的。

例如，10:11:12 之类的值可能看起来像时间值，因为它们的分隔符是 : ，但它也可以表示为一个日期 2010-11-12。

还有，年月日时分秒中的任何一部分只要非法，那么整个值就会归零，例如 date 类型的值如果为 10:45:15 那么就会被转换为 0000-00-00，因为 45 不是有效月份值
上面也提到了，就是日期和时间部分与小数秒部分之间识别的唯一分隔符是小数点
MySQL 服务器会真实的判断一个日期是否有效，而不仅仅是判断月份和日期是否分别在 1 到 12 和 1 到 31 的范围内。

在严格模式下 ( sql_mode=TRADITIONAL ) 无效的月份和日期（例如 '2004-04-31' ）直接会导致一个错误发生，

即使在禁用严格模式后，'2004-04-31' 等无效日期也会被转换为 '0000-00-00' 且生成一个警告
对于 timestamp 类型，MySQL 并不支持月份和日期中的 0 值，也就是没有什么 00 月和 00 日，这些都不是有效的值，会直接导致整体的值被转换为 1970-01-01 00:00:00
对于 datetime 和 timestamp 类型，两位数的年是不确定的日期，因为不知道是属于哪个世纪。

对于两位数的年份，MySQL 使用下面这些规则解释
1. 00-69 范围内的年份值将转换为 2000-2069
2. 70-99 范围内的年份值转换为 1970-1999