I’m trying to extract a datetime from a JSONFIELD “data” in MySQL.
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" | +----------------------------------+
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 | +-----------------------------------------------------------+
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!
Advertisement
Answer
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");