MySQL 拾遗
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

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

转
 2023-03-21  阅读(1)
©版权
原文作者：简单教程 原文地址：https://www.twle.cn/c/yufei/mysqlfav/mysqlfav-basic-distinct.html

在我十年的 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-DD HH:MM:SS 或 YY-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: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. 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-00 ，datetime 类型会转换为 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

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

  • 大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
  • 原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
  • 持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
  • 覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
  • 不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
  • 宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
  • 宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
  • 宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】 即可免费领取。

阅读全文
文章标签： MySQL