select CURDATE() 查询当前年月日
select CURTIME() 查询当前时分秒
select TO_DAYS(‘2022-10-07 19:00:00’) 738800 以下查询出来的时间均包含now()以后的时间
SELECT * from ffy_activity WHERE TO_DAYS(NOW()) - TO_DAYS(end_time) =0 查询出今天的数据
SELECT * from ffy_activity WHERE TO_DAYS(NOW()) - TO_DAYS(end_time) =1 查询出昨天的数据
SELECT * from ffy_activity WHERE TO_DAYS(NOW()) - TO_DAYS(end_time) =2 查询出活动结束时间(end_time)是2天前的记录
SELECT * from ffy_activity WHERE TO_DAYS(NOW()) - TO_DAYS(end_time) =3 查询出3天前的数据
SELECT * from ffy_activity WHERE TO_DAYS(NOW()) - TO_DAYS(end_time) <=3 查询出活动结束时间(end_time)是从3天前开始计算的记录
SELECT * FROM ffy_activity where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(end_time) 查询出活动结束时间(end_time)是从7天前开始计算的记录
SELECT * FROM ffy_activity where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(end_time) 查询出活动结束时间(end_time)是从30天前开始计算的记录
SELECT * FROM ffy_activity WHERE DATE_FORMAT( ‘2022年10月’, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )
/查询出双11那天该用户每小时购买的订单成交数/
SELECT member_id AS ‘用户名’,DATE_FORMAT(create_time,‘%Y-%m-%d %H’) AS ‘时间/小时’, COUNT(*) AS ‘购买数量’ from li_order
select VERSION()
show VARIABLES like ‘%sql_model’