Mysql 对JSON格式字段内部的时间条件筛选
比如某个表里面有create_time字段,且字段对应的类型是timestamp:
... WHERE create_time >= '2020-11-01' AND create_time <= '2020-11-11 00:00:00'
# 或者使用 between and
... WHERE create_time BETWEEN '2020-11-01 00:00:00' AND '2020-11-11 00:00:00'
但有的时间字段是存在json格式的数据里面的
比如有一条数据的operate_time字段的值是这样:"{'dispatchTime':'2020-11-05 10:00:00}"
那么这时候可以用下面两种方法比较
1、 between and
... WHERE JSON_UNQUOTE(JSON_EXTRACT(operate_time, '$.dispatchTime')) BETWEEN '2020-11-01' AND '2020-11-11'
2、unix_timestamp
... WHERE UNIX_TIMESTAMP(JSON_EXTRACT(operate_time, '$.dispatchTime')) BETWEEN UNIX_TIMESTAMP('2020-11-01') AND UNIX_TIMESTAMP('2020-11-11')