1. 为什么时间边界处理这么重要做过数据统计的朋友应该都遇到过这样的场景老板让你统计昨天的订单量你信心满满地跑了个查询结果发现数据对不上。仔细一查才发现原来是时间边界没处理好漏掉了凌晨产生的数据或者多统计了今天凌晨的数据。这种问题在日报、周报等周期性统计中特别常见。时间边界处理的核心难点在于如何准确界定一个时间范围的开始和结束。比如昨天这个时间范围很多人会简单地认为是从昨天0点到今天0点但实际上更精确的定义应该是从昨天0点0分0秒到昨天23点59分59秒。这两种定义看似相似但在数据处理时会产生完全不同的结果。我在实际项目中就踩过这样的坑。有一次做用户活跃度统计需要按天统计UV独立访客数。最初用的方法是WHERE create_time 2023-01-01 AND create_time 2023-01-02结果发现1月1日23:59:59注册的用户被漏掉了。后来改用BETWEEN 2023-01-01 00:00:00 AND 2023-01-01 23:59:59才解决了问题。2. MySQL中获取时间边界的常用方法2.1 获取当天零点获取当天零点是时间处理中最基础的需求。在MySQL中最简单的办法是使用DATE()函数SELECT DATE(NOW()) AS today_zero_time;这个查询会返回当前日期的零点时间。比如今天是2024-08-15那么结果就是2024-08-15 00:00:00。不过要注意DATE()函数返回的是日期类型没有时间部分。如果需要完整的datetime类型可以这样写SELECT TIMESTAMP(DATE(NOW())) AS today_zero_timestamp;2.2 获取当天最后一秒获取当天最后一秒稍微复杂一些需要用到日期加减函数SELECT DATE_ADD(ADDDATE(DATE(NOW()), 1), INTERVAL -1 SECOND) AS today_last_time;这个查询的逻辑是先用DATE(NOW())获取当天日期用ADDDATE(..., 1)加1天得到第二天日期用DATE_ADD(..., INTERVAL -1 SECOND)减去1秒得到当天最后一秒2.3 获取昨天的时间范围统计昨天的数据是业务中最常见的需求之一。获取昨天零点SELECT ADDDATE(DATE(NOW()), -1) AS yesterday_zero_time;获取昨天最后一秒SELECT DATE_ADD(DATE(NOW()), INTERVAL -1 SECOND) AS yesterday_last_time;这里有个小技巧昨天最后一秒其实就是今天零点减1秒。所以上面的查询实际上是用DATE(NOW())获取今天零点然后减去1秒。2.4 处理指定日期的时间边界有时候我们需要处理非当前日期的数据比如统计某个特定日期的数据量。获取指定日期零点SELECT DATE(STR_TO_DATE(2024-08-08 12:13:14, %Y-%m-%d %H:%i:%s)) AS appoint_zero_time;获取指定日期最后一秒SELECT DATE_ADD(ADDDATE(DATE(STR_TO_DATE(2024-08-08 12:13:14, %Y-%m-%d %H:%i:%s)), 1), INTERVAL -1 SECOND) AS appoint_last_time;3. 实战统计昨日数据的正确姿势3.1 数据准备为了更好地演示我们先创建一个测试表并插入一些数据CREATE TABLE cust_test_letter ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 标识, letter_info VARCHAR(1000) DEFAULT NULL COMMENT 来信内容, letter_date DATE DEFAULT NULL COMMENT 来信日期, letter_time DATETIME DEFAULT NOW() COMMENT 来信日期时间, PRIMARY KEY (id) ) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin COMMENT测试来信;插入测试数据INSERT INTO cust_test_letter (id, letter_info, letter_date, letter_time) VALUES (1,需要整改,2024-07-31,2024-07-31 00:00:00), (2,需要整改,2024-08-05,2024-08-05 00:00:00), (3,需要整改,2024-08-06,2024-08-06 10:08:44), (4,需要整改,2024-08-01,2024-08-01 00:00:00), (5,需要整改,2024-08-09,2024-08-09 00:00:00), (6,需要整改,2024-08-12,2024-08-12 00:00:00), (7,需要整改,2024-08-09,2024-08-07 08:57:33), (8,需要整改,2024-08-10,2024-08-07 08:59:36), (9,需要整改,2024-08-07,2024-08-07 08:00:00), (10,需要整改,2024-08-06,2024-08-06 00:00:00), (11,需要整改,2024-08-07,2024-08-07 00:00:00), (12,需要整改,2024-08-01,2024-08-01 00:00:00), (13,需要整改,2024-08-07,2024-08-07 13:00:00), (14,需要整改,2024-08-07,2024-08-07 00:00:00), (15,需要整改,2024-08-08,2024-08-08 00:00:00), (16,需要整改,2024-08-08,2024-08-08 09:09:37);3.2 两种查询方式的对比统计昨天的数据常用的有两种写法第一种是使用大于等于和小于SELECT * FROM cust_test_letter ctl WHERE ctl.letter_time ADDDATE(DATE(NOW()), -1) AND ctl.letter_time DATE(NOW());第二种是使用BETWEENSELECT * FROM cust_test_letter ctl WHERE ctl.letter_time BETWEEN ADDDATE(DATE(NOW()), -1) AND DATE_ADD(DATE(NOW()), INTERVAL -1 SECOND);这两种写法都能正确统计昨天的数据但我个人更推荐第一种。原因有三第一种写法更直观明确表示了时间范围是大于等于昨天零点小于今天零点BETWEEN是包含两端的所以需要特别注意结束时间要减1秒第一种写法在索引利用上通常更好4. 高级技巧与常见问题4.1 处理时区问题如果你的应用是跨时区的时间边界处理会更加复杂。MySQL的时区设置会影响NOW()等函数的返回值。建议在MySQL配置中设置正确的时区应用连接MySQL时也设置相同的时区存储时间数据时尽量使用UTC时间-- 查看当前时区 SELECT global.time_zone, session.time_zone; -- 设置时区例如设置为东八区 SET time_zone 08:00;4.2 性能优化建议时间范围查询通常会用到索引但写法不当可能导致索引失效不要在时间字段上使用函数这会导致索引失效。比如WHERE DATE(create_time) 2024-08-01就不如WHERE create_time 2024-08-01 AND create_time 2024-08-02对于大表考虑使用分区表按时间范围分区复合索引中时间字段通常放在最后4.3 处理NULL值如果你的时间字段允许NULL查询时需要特别注意-- 这样会漏掉letter_time为NULL的记录 SELECT * FROM cust_test_letter WHERE letter_time BETWEEN ... AND ...; -- 正确的写法应该加上OR条件 SELECT * FROM cust_test_letter WHERE (letter_time BETWEEN ... AND ...) OR letter_time IS NULL;5. 实际案例统计周报数据假设我们需要统计每周的数据周一到周日为一个统计周期。获取本周一时间零点SELECT DATE_SUB(DATE(NOW()), INTERVAL WEEKDAY(NOW()) DAY) AS this_monday;获取下周一零点即本周日最后一秒加1秒SELECT DATE_ADD(DATE_SUB(DATE(NOW()), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY) AS next_monday;统计本周数据的查询SELECT COUNT(*) AS weekly_count FROM cust_test_letter WHERE letter_time DATE_SUB(DATE(NOW()), INTERVAL WEEKDAY(NOW()) DAY) AND letter_time DATE_ADD(DATE_SUB(DATE(NOW()), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 7 DAY);这个查询利用了MySQL的WEEKDAY()函数它返回当前日期是本周的第几天周一为0周日为6。通过这个函数我们可以准确定位本周一的日期。