Chaining JSON_EXTRACT with CAST or STR_TO_DATE fails
You have to use JSON_UNQUOTE
select CAST( JSON_UNQUOTE( JSON_EXTRACT(data, "$.new_time")) as DATETIME) from analytics_calendaranalytics limit 10;
Would work. I am saying would because you haven't provided sample data. I tried as follows:
select @js := JSON_OBJECT('new_time',"2016-09-30T04:00:00+00:00" );select CAST(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) as DATETIME);
The following query also works
select STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) ,"%Y-%m-%d");
More READABLE way to do this is using ->>;
SELECT CAST(data->>'$.new_time' AS DATETIME) FROM analytics limit 10;
Example:
mysql> CREATE TABLE analytics (data json not null);Query OK, 0 rows affected (0.03 sec)mysql> INSERT INTO analytics (data) VALUES ('{"new_time": "2021-04-21T04:00:00+00:00"}');Query OK, 1 row affected (0.01 sec)mysql> SELECT CAST(data->>'$.new_time' AS DATETIME) FROM analytics limit 10;+---------------------------------------+| CAST(data->>'$.new_time' AS DATETIME) |+---------------------------------------+| 2021-04-21 04:00:00 |+---------------------------------------+1 row in set, 1 warning (0.00 sec)