超实用的SQL语法3

SQL查询当天的数据集合

and date_format(create_time,"%Y-%m-%d")=date_format(now(),"%Y-%m-%d")

SQL查询最近一小时的数据集合

and create_time > DATE_SUB(NOW(),INTERVAL  1 HOUR)

SQL查询最近两小时的数据集合

and TIMESTAMPDIFF(HOUR,stay_time,now()) <=2

SQL查询最近24小时的数据集合

and create_time > DATE_SUB(NOW(),INTERVAL  24 HOUR)

SQL查询最近7天的数据集合

and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)

SQL查询最近一个月的数据集合

and DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(create_time)

SQL查询指定年、月的数据集合

and where YEAR(stay_time)='2020' and MONTH(stay_time)='11'

SQL查询以分钟为单位差的数据集合

select 
DATE_FORMAT(stay_time, '%Y-%m-%d %H:%i') AS beginTime,
DATE_FORMAT(leave_time, '%Y-%m-%d %H:%i') AS endTime
from  table_name where ... GROUP BY beginTime , endTime