MySQL语句获取今天的0点和23:59:59

最新推荐文章于 2024-08-27 15:18:26 发布

κ青橘柠檬

最新推荐文章于 2024-08-27 15:18:26 发布

阅读量2.1k

点赞数 2

分类专栏： mysql

本文链接：https://blog.csdn.net/Arry_Coding/article/details/111988356

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1.获取今天的0点和今天的23:59:59

select a.order_sn,a.is_delete from nur_pro_trade.ns_order_service_deduct a left join ptcs_pro_extended.ns_attendant_service_record b on a.order_sn=b.order_sn where 
(a.service_start_date >=DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') and a.service_start_date <= DATE_SUB(DATE_ADD(CURDATE(), INTERVAL 1 DAY),INTERVAL 1 SECOND)) and a.is_delete = 0 and b.id is null

函数解析：DATE_FORMAT(CURDATE(),’%Y-%m-%d %H:%i:%s’)
1》select DATE_FORMAT(CURDATE(),’%Y-%m-%d %H:%i:%s’)；查出来是今天的0点
2》DATE_SUB(DATE_ADD(CURDATE(), INTERVAL 1 DAY),INTERVAL 1 SECOND)) ；今天加一天减一秒，就是今天的23:59:59
获取明天的0点和今天的23:59:59

2.获取明天的0点和23：59：59
select a.order_sn,a.is_delete from nur_pro_trade.ns_order_service_deduct a left join ptcs_pro_extended.ns_attendant_service_record b on a.order_sn=b.order_sn where
(a.service_start_date >=DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),’%Y-%m-%d %H:%i:%s’) and a.service_start_date <= DATE_SUB(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY), INTERVAL 1 DAY),INTERVAL 1 SECOND)) and a.is_delete = 0 and b.id is null

–》DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),’%Y-%m-%d %H:%i:%s’)；明天的0点

–》DATE_SUB(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY), INTERVAL 1 DAY),INTERVAL 1 SECOND)；明天的23:59:59