在JSON mysql中查找两个iso日期

Question

下面是我的JSON保存在mysql表的response列

{
"data": {
"submit": false,
"lastName": "abc",
"firstName": "xys"
},
"metadata": {},
"createDate": "2021-04-23T00:00:00+00:00"
}

现在我想查询它的between条件我试着写下面的query

SELECT * FROM form_response WHERE DATE_FORMAT(response->'$.data.createDate', '%Y-%m-%dT%TZ') BETWEEN "2021-04-22T00:00:00+00:00" AND "2021-04-23T00:00:00+00:00"

但是我得到空白的响应。

感谢您的帮助

Answer 1

SELECT * FROM form_response WHERE DATE_FORMAT(SUBSTR(response->'$.createDate'，2,CHAR_LENGTH(response->'$.createDate')-2)， '%Y-%m-%dT%TZ') BETWEEN " 20121-04-22 t00:00:00+00:00"one_answers"2021 - 04 - 23 t00:00:00 + 00:00"

Answer 2

response->'$.data.createDate'已经返回null了，所以之后什么都不重要了。根据你的数据结构，你想要的是response->'$.createDate'。另外，DATE_FORMAT()将日期转换为字符串。我想你需要STR_TO_DATE()。最后，您提供的日期格式标识符与data使用的格式不匹配。

不要把你的代码当成黑盒。您可以调试每个处理步骤:

SELECT
response->'$.createDate' AS string,
STR_TO_DATE(response->'$.createDate', '"%Y-%m-%dT%T+00:00"') AS data
FROM form_response;

+-----------------------------+---------------------+
| string                      | date                |
+-----------------------------+---------------------+
| "2021-04-23T00:00:00+00:00" | 2021-04-23 00:00:00 |
+-----------------------------+---------------------+

在你的情况下，你可以进一步简化它，因为你使用的是MySQL识别的标准日期格式:

SELECT
response->'$.createDate' AS string,
CAST(response->'$.createDate' AS DATETIME) AS date
FROM form_response;