使用CAST或STR_TO_DATE链接JSON_EXTRACT失败 [英] Chaining JSON_EXTRACT with CAST or STR_TO_DATE fails
本文介绍了使用CAST或STR_TO_DATE链接JSON_EXTRACT失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试从MySQL中的JSONFIELD数据"中提取日期时间.
I'm trying to extract a datetime from a JSONFIELD "data" in MySQL.
但是,如果我执行简单的JSON_EXTRACT,则返回字段类型为JSON.
If I do a simple JSON_EXTRACT however, the return field type is a JSON.
mysql> select JSON_EXTRACT(data, "$.new_time") from analytics limit 10;
+----------------------------------+
| JSON_EXTRACT(data, "$.new_time") |
+----------------------------------+
| NULL |
| "2016-09-30T04:00:00+00:00" |
| "2016-09-29T05:30:00+00:00" |
| NULL |
| "2016-10-01T05:30:00+00:00" |
| "2016-09-27T23:00:00+00:00" |
| NULL |
| "2016-09-23T01:30:00+00:00" |
| "2016-09-23T04:00:00+00:00" |
| "2016-09-27T01:30:00+00:00" |
+----------------------------------+
我想将其转换为MySQL DATETIME.但是,如果我链接JSON_EXTRACT和STR_TO_DATETIME,我会得到所有NULL值:
I want to convert this to a MySQL DATETIME. But if I chain JSON_EXTRACT and STR_TO_DATETIME, I get all NULL values:
mysql> select STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") from analytics_calendaranalytics limit 10;
+-----------------------------------------------------------+
| STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") |
+-----------------------------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+-----------------------------------------------------------+
同样,以DATETIME进行CAST也会失败:
Likewise, a CAST as DATETIME also fails:
mysql> select CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) from analytics_calendaranalytics limit 10;
+----------------------------------------------------+
| CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) |
+----------------------------------------------------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+----------------------------------------------------+
当我从字符串值开始时,这两个命令都起作用:
Both these commands work when I start with the string value:
mysql> select CAST("2016-09-30T04:00:00+00:00" as DATETIME);
+-----------------------------------------------+
| CAST("2016-09-30T04:00:00+00:00" as DATETIME) |
+-----------------------------------------------+
| 2016-09-30 04:00:00 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d");
+------------------------------------------------------+
| STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d") |
+------------------------------------------------------+
| 2016-09-30 |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
将感谢您为解决此问题提供的任何帮助!
Would appreciate any help in resolving this!
推荐答案
查看全文