获取当天00:00:00的时间:
SELECT DATE_FORMAT(NOW(),"%Y-%m-%d 00:00:00");
获取当天23:59:59的时间:
SELECT DATE_FORMAT(NOW(),"%Y-%m-%d 23:59:59");
获取前一天00:00:00到23:59:59的数据:
SELECT DATE_FORMAT(时间字段,"%Y-%m-%d %H:%i:%s") AS time,COUNT(统计数据的字段) AS Counts FROM `表名`
WHERE 1=1
AND 时间字段>=DATE_FORMAT((NOW() - interval 1 DAY),"%Y-%m-%d 00:00:00")
AND 时间字段<=DATE_FORMAT((NOW() - interval 1 DAY),"%Y-%m-%d 23:59:59")
GROUP BY DATE_FORMAT(时间字段,"%Y-%m-%d %H:%i:%s");
获取当天时间00:00:00到当前时间的小时时间的数据:
SELECT DATE_FORMAT(时间字段,"%H:%i") AS time,COUNT(统计数据的字段) AS Counts FROM `表名`
WHERE 1=1
AND 时间字段>=DATE_FORMAT(NOW() ,"%Y-%m-%d 00:00")
AND 时间字段<=DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i")
GROUP BY DATE_FORMAT(时间字段,"%H:%i");
查询7天中每一天status=1的数据有多少条
SELECT DATE_FORMAT(create_time,"%m-%d"),COUNT(DISTINCT sensor_number) FROM `interface_data_list`
WHERE 1=1
AND create_time>NOW()-INTERVAL 7 day
AND create_time<=NOW()
AND `status`=1
GROUP BY DATE_FORMAT(create_time,"%m-%d")