专栏首页SEian.G学习记录MySQL 8.0中DATE,DATETIME和 TIMESTAMP类型和5.7之间的差异

MySQL 8.0中DATE,DATETIME和 TIMESTAMP类型和5.7之间的差异

MySQL中DATE,DATETIME和 TIMESTAMP类型都和时间有关。本文介绍MySQL 8.0和MySQL 5.7之间的差异;本文MySQL实验环境为8.0.23;

MySQL允许对DATETIME和 TIMESTAMP值使用小数秒 , 精度最高为微秒(6位数)

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

DATE 格式为:‘YYYY-MM-DD’,支持的范围是 ‘1000-01-01’到 ‘9999-12-31’。 DATETIME[(fsp)] 日期和时间组合。格式为:’YYYY-MM-DD hh:mm:ss’,支持的范围是 ‘1000-01-01 00:00:00.000000’到 ‘9999-12-31 23:59:59.999999’。 fsp指定一个介于0到6之间的可选值,以指定小数秒精度。值为0表示没有小数部分。如果省略,则默认精度为0。 DATETIME可以使用DEFAULT和 ON UPDATE列定义子句指定 自动初始化和更新到列的当前日期和时间 TIMESTAMP[(fsp)] 时间戳。格式为:’YYYY-MM-DD hh:mm:ss’。范围是’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。TIMESTAMP值存储为自纪元(’1970-01-01 00:00:00’UTC)以来的秒数。TIMESTAMP不能代表值’1970-01-01 00:00:00’,因为这是等同于从所述历元和值00秒被保留用于表示’0000-00-00 00:00:00’,该“零” TIMESTAMP值。 fsp指定一个介于0到6之间的可选值,以指定小数秒精度。值为0表示没有小数部分。如果省略,则默认精度为0。 服务器处理TIMESTAMP 定义的方式取决于explicit_defaults_for_timestamp 系统变量的值 (请参见 第5.1.8节“服务器系统变量”)。 如果 explicit_defaults_for_timestamp 启用,则不会自动将DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性分配 给任何 TIMESTAMP列。它们必须明确包含在列定义中。同样,任何 TIMESTAMP未明确声明为NOT NULL允许 NULL值的值。 如果 explicit_defaults_for_timestamp 禁用,则服务器TIMESTAMP 将按以下方式处理: 除非另有说明,如果未显式分配值,则表中的第一 列TIMESTAMP被定义为自动设置为最新修改的日期和时间。这TIMESTAMP 对于记录“INSERT或” UPDATE操作的时间戳很有用 。也可以TIMESTAMP通过为其分配NULL值来将任何列设置为当前日期和时间 ,除非已使用NULL,允许NULL值的属性对其进行 了定义。 可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句指定自动初始化和更新到当前日期和时间。默认情况下,第一TIMESTAMP 列具有这些属性,如前所述。但是,TIMESTAMP可以将表中的任何列定义为具有这些属性。

小数部分应始终与其余时间间隔一个小数点;无法识别其他小数秒分隔符。

该TIMESTAMP和DATETIME 数据类型提供自动初始化和更新到当前的日期和时间。后续文章会进行讲解;

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

从MySQL 8.0.19开始,可以在向表中插入TIMESTAMP和 DATETIME值时指定时区偏移量。偏移量被附加到datetime文字的时间部分,中间没有空格,并且使用与设置time_zone系统变量相同的格式,但以下情况除外:

* 如果小时值小于10,则需要前导零。 * 该值’-00:00’被拒绝。 * 时区名称,例如’EET’和 ‘Asia/Shanghai’不能使用;‘SYSTEM’也不能在这种情况下使用。

从MySQL8.0.22开始,插入值的月份、日 部分或两者都不能为零,这是强制执行的,不管服务器SQL模式如何设置。

此示例演示如何使用不同的时区设置将带有时区偏移的datetime值插入TIMESTAMP和datetime列,然后检索它们:

mysql>CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.09 sec)
 
mysql>CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)
 
mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.01 sec)
 
mysql>INSERT INTO ts(col) VALUES('2020-04-25 09:10:10'),('2020-04-25 10:10:10+05:30'),('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql>SET @@time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)
 
mysql>INSERT INTO ts(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)
 
mysql>INSERT INTO dt(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql>SET @@time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)
 
mysql>INSERT INTO dt(col) VALUES ('2020-04-25 10:10:10'),('2020-04-25 10:10:10+05:30'), ('2020-04-25 10:10:10-08:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql>SET @@time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.00 sec)
 
mysql>SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| CST                |
+--------------------+
1 row in set (0.01 sec)
 
mysql>SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-04-25 10:10:10 |          1587780610 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
| 2020-04-25 10:10:10 |          1587780610 |
| 2020-04-25 04:40:10 |          1587760810 |
| 2020-04-25 18:10:10 |          1587809410 |
+---------------------+---------------------+
6 rows in set (0.00 sec)
 
mysql>SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-04-25 09:10:10 |          1587777010 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
| 2020-04-25 18:10:10 |          1587809410 |
| 2020-04-25 12:40:10 |          1587789610 |
| 2020-04-26 02:10:10 |          1587838210 |
+---------------------+---------------------+
6 rows in set (0.00 sec)

当选择 datetime类型时,即使插入时使用了偏移量,也不会显示偏移量。

支持的偏移值范围是 -13:59至+14:00。

包含时区偏移量的Datetime文字被准备好的语句接受为参数值。

如果SQL模式允许此转换,则将无效的日期、日期时间或时间戳值转换为相应类型的“零”值(’0000-00-00’或’0000-00-00 00:00:00’)。精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE模式;

在MySQL 8.0.22和更高版本,可以转换 TIMESTAMP值UTC DATETIME使用提取它们的值 CAST()与AT TIME ZONE操作,如下所示:

mysql>SELECT col,CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | ut                  |
+---------------------+---------------------+
| 2020-04-25 09:10:10 | 2020-04-25 01:10:10 |
| 2020-04-25 12:40:10 | 2020-04-25 04:40:10 |
| 2020-04-26 02:10:10 | 2020-04-25 18:10:10 |
| 2020-04-25 18:10:10 | 2020-04-25 10:10:10 |
| 2020-04-25 12:40:10 | 2020-04-25 04:40:10 |
| 2020-04-26 02:10:10 | 2020-04-25 18:10:10 |
+---------------------+---------------------+
6 rows in set (0.00 sec)

注意MySQL中日期值解释的某些属性:

* MySQL允许对指定为字符串的值使用“放松”格式,其中任何标点字符都可以用作日期部分或时间部分之间的分隔符。在某些情况下,这种语法可能是欺骗的。例如,例如“10:11:12”之类的值可能因为:,看起来像一个时间值,但如果在日期上下文中使用,则解释为“2010-11-12”年。值“10:45:15”转换为“0000-00-00”,因为“45”不是有效月份。 * 在日期和时间部分与小数秒部分之间唯一识别的分隔符是小数点。 * 服务器要求月份和日期值有效,而不仅仅是分别在1到12和1到31范围内。禁用严格模式后,无效日期(例如) ‘2004-04-31’将转换为 ‘0000-00-00’并生成警告。启用严格模式后,无效日期会产生错误。要允许这样的日期,请启用 ALLOW_INVALID_DATES。 * MySQL不接受TIMESTAMP值在day或month列中包含零的值或不是有效日期的值。唯一的例外是特殊的“零”值 ‘0000-00-00 00:00:00’,如果SQL模式允许该值。精确的行为取决于是否启用了严格SQL模式和NO_ZERO_DATE无零日期SQL模式; * 包含两位数年份值的日期是不明确的,因为世纪是未知的。MySQL使用以下规则解释两位数的年份值: 范围内的年值00-69变为 2000-2069。 范围内的年值70-99变为 1970-1999。

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEianG

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-04-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL日期和时间类型笔记

    最近在看《MySQL技术内幕:SQL编程》并做了笔记,这是一篇笔记类型博客,分享出来方便自己复习,也可以帮助其他人

    SmileNicky
  • 【Mysql】MySQL中 TIMESTAMP类型 和 DATETIME类型 的区别

    TIMESTAMP:把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。

    瑞新
  • 第11章、数据类型

    关键字 INT 是 INTEGER 的别名,关键字 DEC 和 FIXED 是 DECIMAL的别名。 在 MyISAM/MEMORY/InnoDB和NDB表...

    幺鹿
  • MySQL中DATETIME与TIMESTAMP

    真理永远都不过时,今天由于工作的事情涉及到了这里,印象中只记得DATETIME类型占用8字节,TIMESTAMP类型占用4字节,心想这么久没有更新的知识万一过时...

    江湖安得便相忘
  • MySQL中DATETIME与TIMESTAMP

    真理永远都不过时,今天由于工作的事情涉及到了这里,印象中只记得DATETIME类型占用8字节,TIMESTAMP类型占用4字节,心想这么久没有更新的知识万一过时...

    暮雨
  • 利用logstash将mysql多表数据增量同步到es

    我的数据库是5.7版本,我这里下载5.1.47的驱动了,当然如果你们的数据库是8.0以上的版本,那么就下相应的版本就行

    黎明大大
  • 时间精度引起MySQL主从不一致问题剖析

    1. 主从数据不一致          近日接报某实例一个datetime字段主从数据不一致,其它数据暂未发现异常。第一反应可能是人为修改,如果用户有高权限帐...

    腾讯数据库技术
  • 由一条create语句的问题对比mysql和oracle中的date差别 (r7笔记第26天)

    今天开发的同事提交过来一个sql变更,在部署的时候发现了一个问题。 语句是一个简单的create语句 CREATE TABLE `test_user` ( ...

    jeanron100
  • mysql 关于时间类型的刨坑之路

    前两天有做一个基于binglog的数据库实时同步,一张老数据表里有DATETIME、TIMESTAMP不同的时间字段类型,看起来值都是一样的,并且默认值都设置的...

    用户2825413
  • MySQL数据库结构设计

    在编码过程中,如果MySQL数据结构设计不好的话,会大大影响开发人员编码效率。比如说MySQL数据库表设计不规范,创建时间字段设计成cjsj,创建者字段设计成c...

    用户2032165
  • MySQL入门详解(一)---mysql的语言

    当客户端连接到服务器是,MySQL访问控制有两个阶段:连接验证 对用户名密码,请求验证 对权限验证

    步履不停凡
  • mysql升级到最新版(mysql8.0.25)

    MYSQL 5.6 --> MySQL 5.7 --> MySQL8.0.x

    wangwei-dba
  • DB2 Vs MySQL系列 | MySQL与DB2的数据类型对比

    随着MySQL数据库的应用越来越广泛,DB2向MySQL数据库的迁移需求也越来越多。进行数据库之间迁移的时候,首先遇到的并且也是最基本最重要的就是两种数据库数据...

    数据和云
  • mysql学习笔记

    InnoDB引擎与MyISAM引擎 mysql是关系型数据库。其中的存储引擎可以show engines来查看。我的版本是5.6.26的,查看版本用selec...

    wangxl
  • MySQL 8.0.19亿级数据如何秒速增加字段?

    今天主要介绍一下MySQL 8.0.19 instant add column的新特性,基于亿级数据秒速增加字段,下面一起来看看吧~

    IT大咖说
  • 数据库存储时间你用对了吗?

    我们平时在开发中不可避免的要存储时间,比如我们要记录某条数据的创建时间、更新时间等等。数据库中有多种数据类型可以存储时间,那不同数据类型我们要怎么选择?

    Java识堂
  • MySQL中timestamp和datetime,你用的对么?

    在MySQL中,时间是咱们用到最多的类型,建表时,对于时间字段类型的选择,你是如何选择的呢?有人会说timestamp,也有人会说datetime,那么...

    xcbeyond
  • 数据库时区那些事儿 - MySQL的时区处理

    当JVM时区和数据库时区不一致的时候,会发生什么?这个问题也许你从来没有注意过,但是当把Java程序容器化的时候,问题就浮现出来了,因为目前几乎所有的Docke...

    颇忒脱
  • Oracle 与 MySQL 的差异分析(2):数据类型

    在Oracle中,一般使用 integer、 int或者 number(N),MySQL 也支持 integer 和 int,但不支持 number 或 num...

    一头小山猪

扫码关注云+社区

领取腾讯云代金券