• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 7个月前 (04-25) 458次浏览 已收录 0个评论

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。

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


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL 8.0中DATE,DATETIME和 TIMESTAMP类型和5.7之间的差异
喜欢 (4)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址