mysql获取当天,昨天,本周,本月,上周,上月的起始时间

-- 今天
select DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
select DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';

-- 昨天
select DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
select DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';

-- 上周
select DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
select DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';

-- 本周
-- select DATE_FORMAT( DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
-- select DATE_FORMAT( DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), '%Y-%m-%d 23:59:59') AS '本周末';

-- 本周
select DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
select DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';

-- 上月
select DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
select DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';

-- 本月
select DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
select DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

-- 查询本月
SELECT sum(money) FROM  user where month(date)=(month(NOW()));

-- 本星期
SELECT * FROM  user where week(date)=(week(NOW()));

-- 上个月
SELECT * FROM user WHERE month(date)!=month(NOW()) and (date BETWEEN (date_sub(NOW(),interval 1 month)) and NOW());

-- 上周
SELECT * FROM user WHERE week(date)!=week(NOW()) and (date BETWEEN (date_sub(NOW(),interval 1 week)) and NOW());


--今天

select * from 表名 where to_days(时间字段名) = to_days(now());

--昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

--7天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

--近30天

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

--本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

--上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1