时间戳

首页 > 解决方案 > 如何从 json 提取日期在 mysql 中生成时间戳类型的虚拟列？

mysql - 如何从 json 提取日期在 mysql 中生成时间戳类型的虚拟列？

问题描述

我的 mysql DB 表 BLAH 中有一个 json 类型的字段 LOREM。

LOREM 有一个 createdOn 属性，它是一个格式如下的日期：（2020-03-05T04:30:00.000+0000 这是通过运行命令检索到的值：

SELECT LOREM ->>'$.createdOn' FROM BLAH;)

我正在尝试为 created on 创建一个虚拟生成的列，以便查询变得更容易一些。创建表时，我尝试将其放入 ddl 中：

CREATED_ON timestamp(3) GENERATED ALWAYS AS (CAST(LOREM ->> '$.createdOn' AS DATETIME)) VIRTUAL, 但是在插入条目时出现错误：

1292 截断不正确的日期时间值：'2020-03-05T04:30:00.000+0000'

我尝试将列时间戳精度从 3 更改为 6，但这没有用。我也试过

CREATED_ON timestamp(3) GENERATED ALWAYS AS (TIMESTAMP(CAST(LOREM ->> '$.createdOn' AS DATETIME))) VIRTUAL,，但得到了同样的错误。

知道为什么会发生这种情况以及我应该如何解决它？

标签： mysqlsqljsondatabasejson-extract

解决方案

11.2.2 DATE、DATETIME 和 TIMESTAMP 类型

...

从 MySQL 8.0.19 开始，您可以在将 TIMESTAMP 和 DATETIME 值插入表时指定时区偏移量。偏移量附加到日期时间文字的日期部分，没有内部空格，并使用与设置 time_zone 系统变量相同的格式，但以下情况除外：

  • 对于小于 10 的小时值，需要前导零。
  • 值“-00:00”被拒绝。
  • 不能使用“EET”和“Asia/Shanghai”等时区名称；'SYSTEM' 也不能在这种情况下使用。

...

2020-03-05T04:30:00.000+0000 != 2020-03-05T04:30:00.000+00:00

请参阅dbfiddle。

推荐阅读