1. 首页
  2. mysql基础

06-六、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 )

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

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

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

DATE、DATETIME 和 TIMESTAMP 的格式

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

其次,MySQL 可以识别多种格式的 datedatetimetimestamp ,具体支持的格式有

1、对于 date 类型,支持 YYYY-MM-DDYY-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

同时还支持 YYYYMMDDYYMMDD 的字符串形式,例如 '20180913''180913'

还支持 YYYYMMMDDYYMMDD 的数字格式,例如 20180913180913

2、对于 datetimetimestamp 类型,支持 YYYY-MM-DD HH:MM:SSYY-MM-DD HH:MM:SS,一起它们的变形

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

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

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

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

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

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

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

因为 MySQL 同时支持超大整数类型,所以,数字形式的 YYYYMMDDHHMMSSYYMMDDHHMMSS 也是被支持的,例如 20180913211510180913211510 被认为与 2018-09-13 21:15:10 是等价的

DATE、DATETIME 和 TIMESTAMP 的特征

从上面的格式中可以看出,date 类型就是只有 年月日 没有 时分秒,MySQL 以 YYYY-MM-DD 格式检索并显示 date 类型的值。支持的范围是 1000-01-019999-12-31

datetime 类型通常包含完整的日期时间,也就是能够完整的表达某一个时刻。MySQL 以 YYYY-MM-DD HH:MM:SS 格式检索并显示 datetime 类型的值,支持的范围是 1000-01-01 00:00:009999-12-31 23:59:59

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

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

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.0000009999 -12-31 23:59:59.999999 而 timestamp 类型的范围则为 1970-01-01 00:00:01.0000002038-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、DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 insert 操作时自动插入当前日期时间
2、DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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-00datetime 类型会转换为 0000-00-00 00:00:00 ,而 timestamp 则会转换为 1970-01-01 00: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-01 00:00:00
6、对于 datetime 和 timestamp 类型,两位数的年是不确定的日期,因为不知道是属于哪个世纪。

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

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

希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:

  1. 关注我们的 GitHub 博客,让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
  3. 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
  4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区,是给开发者用的 Hacker News,技术文章由为你筛选出最优质的干货,其中包括:Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者,你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有,如若转载,请注明出处

    转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题:06-六、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 )

    链接:https://www.javajike.com/article/1622.html

« 07-七、傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 中 )
05-五、被 MySQL sql_mode 深深伤害( 下 )»

相关推荐

QR code