mysql - 如何在转换为数据类型 DATETIME 的表的新列中执行时间戳的 SQL INSERT
问题描述
我有一个数据类型为 VARCHAR 的列“TIMESTAMP”的表:
CREATE TABLE Table1
(`ID` int, `TIMESTAMP` varchar(24), `TOPIC` varchar(30), `PAYLOAD` numeric, `UNIT` varchar(5), `SENSOR` varchar(25), `DerivedValue` int, `DerivedValueDesc` varchar(17))
;
INSERT INTO Table1
(`ID`, `TIMESTAMP`, `TOPIC`, `PAYLOAD`, `UNIT`, `SENSOR`, `DerivedValue`, `DerivedValueDesc`)
VALUES
(17463, '2019-11-28T17:58:12.776Z', 'Wago-855-9150', 475.29334, 'W', '"Wago 855-9150/2000-0701"', 1, '"Produktion ja"'),
(17464, '2019-11-28T17:58:13.675Z', 'ifm-O5D150', 19.0625, 'cm', '"ifm O5D150"', 1, '"Tür offen"'),
(17465, '2019-11-28T17:58:13.675Z', 'Keyence-FD-Q20C', 0, 'L/min', '"Keyence FD-Q20C"', 0, '"Durchfluss nein"'),
(17466, '2019-11-28T17:58:14.676Z', 'ifm-O5D150', 23.0625, 'cm', '"ifm O5D150"', 0, '"Tür zu"'),
(17467, '2019-11-28T17:58:14.676Z', 'Keyence-FD-Q20C', 0, 'L/min', '"Keyence FD-Q20C"', 0, '"Durchfluss nein"'),
(17468, '2019-11-28T17:58:14.776Z', 'Wago-855-9150', 569.14666, 'W', '"Wago 855-9150/2000-0701"', 1, '"Produktion ja"')
;
现在发现查询表非常耗时:
SELECT PAYLOAD FROM Table1 WHERE TOPIC LIKE '%temperature%' AND CAST(TIMESTAMP AS datetime) between '2019-09-29 00:00:00' and '2019-09-29 00:59:59';
所以我们的想法是创建一个额外的列并将时间戳存储在数据类型 DATETIME 中:
ALTER TABLE Table1 ADD `DATETIME` DATETIME AFTER `TIMESTAMP`;
然后我想用来自 TIMESTAMP 列的转换数据填充新列:
INSERT INTO Table1(DATETIME) SELECT CAST(`TIMESTAMP` AS DATETIME) FROM Table1;
然后我收到错误消息:
错误:ER_TRUNCATED_WRONG_VALUE:截断不正确的日期时间值:'2019-11-28T17:58:12.776Z'
在https://www.db-fiddle.com/和http://sqlfiddle.com/中对查询进行原型设计时出现此错误。我正在使用 MySQL 数据库 v5.7。
有趣的是,这个查询有效:
SELECT `TIMESTAMP`, CAST(`TIMESTAMP` AS DATETIME) AS `MYDATETIME` FROM Table1;
但我需要新列中的数据而不是查询中的数据。
我的错误是什么?我认为最好不要采用像 STR_TO_DATE 这样的字符串操作,因为对秒数的取整会产生不确定的后果。
解决方案
唯一不同意 MySQL 的日期文字格式的部分是'Z'
末尾。
mysql> select cast('2019-11-28T17:58:12.776Z' as datetime);
+----------------------------------------------+
| cast('2019-11-28T17:58:12.776Z' as datetime) |
+----------------------------------------------+
| 2019-11-28 17:58:13 |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Warning (Code 1292): Truncated incorrect datetime value: '2019-11-28T17:58:12.776Z'
但是修剪一下'Z'
就可以了:
mysql> select cast(trim(trailing 'Z' from '2019-11-28T17:58:12.776Z') as datetime);
+----------------------------------------------------------------------+
| cast(trim(trailing 'Z' from '2019-11-28T17:58:12.776Z') as datetime) |
+----------------------------------------------------------------------+
| 2019-11-28 17:58:13 |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
如果要保留毫秒,则转换为datetime(3)
:
mysql> select cast(trim(trailing 'Z' from '2019-11-28T17:58:12.776Z') as datetime(3));
+-------------------------------------------------------------------------+
| cast(trim(trailing 'Z' from '2019-11-28T17:58:12.776Z') as datetime(3)) |
+-------------------------------------------------------------------------+
| 2019-11-28 17:58:12.776 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
推荐阅读
- javascript - 无法检测到错误
- android - 防止 Recycler 的父级在滚动时折叠
- javascript - 页面左侧的基本垂直滚动指示器
- python - 这个 Python 函数从列表中删除了一个意外的值,而不是预期的索引位置 - 嵌套索引?
- python - 如何使用 Python 选择 PDF 中搜索文本的值?
- php - 如何为此问题编写 htaccess 文件?
- javascript - JS 验证仅允许字母和特定符号 + 更改输入
- redux - 路由更改时使用新的路由参数更新 redux 状态
- excel - 可变数量的嵌套循环
- php - 如何防止特定单词搜索结果 laravel