获取上周三到本周三的数据，格式化时间sql

诗酒趁少年の

已于 2023-07-17 17:04:07 修改

阅读量641

点赞数

文章标签： sql mysql

于 2022-04-07 14:17:13 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45290168/article/details/124015238

版权

获取上周三到本周三的数据

SELECT * FROM table a WHERE 
a.time >= (SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 5), '%Y-%m-%d 12:00:00'))
AND a.time <= (SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) - 2), '%Y-%m-%d 12:00:00'));

(SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 5), '%Y-%m-%d 12:00:00')) AS '上周三';
(SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) - 2), '%Y-%m-%d 12:00:00')) AS '本周三';

select SUBDATE(subdate(curdate(),date_format(curdate(),'%w') - 1),5)  AS '上周三不带时间';
select SUBDATE(subdate(curdate(),date_format(now(),'%w') - 1),-2)  AS '本周三不带时间';

格式化时间

比如：
SELECT 
DATE_FORMAT(a.time,'%Y-%m-%d %h:%m:%s') AS '时间'
FROM table a WHERE 
---------------------------------------
DATE_FORMAT(a.time,'%Y-%m-%d %h:%m:%s')