06、DATE、DATETIME 和 TIMESTAMP ( 上 )

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

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

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

DATE、DATETIME 和 TIMESTAMP 的格式

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

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

1、 对于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。

2、 对于datetime和timestamp类型,支持YYYY-MM-DDHH:MM:SS或YY-MM-DDHH: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

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

1、 日期和时间部分与小数秒部分之间的唯一可用的分隔符号是小数点(.)例如2018-09-1321:15:10.11;
2、 日期与时间部分的分隔符不一定就要使用空格(''),还可以使用字符T,例如2018-09-1321: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
);

1、 DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP指示在insert操作时自动插入当前日期时间;
2、 DEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP指示在update操作时自动更新为当前日期时间;

DATETIME 和 TIMESTAMP 的时区问题

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

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

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

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

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

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

DATE、DATETIME 和 TIMESTAMP 的注意事项

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

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

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

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

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

对于两位数的年份,MySQL 使用下面这些规则解释

1、 00-69范围内的年份值将转换为2000-2069;
2、 70-99范围内的年份值转换为1970-1999;