上周排查一个线上问题，用户反馈"昨天的订单一条都没查到"，但数据库里明明躺着两千多条。最后定位下来，不是数据丢了，也不是接口挂了，而是那个查询条件把时间段写成了>= '2024-05-20 00:00:00' AND <= '2024-05-20 00:00:00'——起止时间一模一样，区间宽度为零。这种一看就想笑、笑完又想哭的错误，在时间字段查询这件事上几乎每天都在发生。
围绕时间字段做指定时间段的查询，看起来是最基础的 SQL 操作，实际上它牵扯的东西比想象中多得多：区间的开闭语义、日期与时间戳的类型差异、时区偏移、函数包字段导致的索引失效、Java 侧格式化时的线程安全、深分页下的性能塌方。这篇文章不打算写成教科书，我想把这几年在订单、日志、监控、报表这几类场景里踩过的时间坑系统性地捋一遍，把每一步"为什么这么写"讲透。无论你是刚开始写 CRUD 的新人，还是带团队做数据平台的老手，下面这些内容应该都能直接拿去对照自己的代码。
1. 时间段到底是什么：先把区间语义对齐再动手写SQL
很多人写时间查询是"手感驱动"——看到需求说"查5月20日的数据"，条件里就顺手敲个BETWEEN '2024-05-20' AND '2024-05-20'。跑出来结果是空的，还以为是没数据。问题的根子不在语法，在于"时间段"这个词本身就有歧义，业务方说的和数据库理解的往往不是一回事。
1.1 左闭右开是工程上最省心的选择
先明确一个术语。所谓"时间段"，数学上是区间，工程上必须指定端点是否包含：
| 区间写法 | 含义 | 是否包含起点 | 是否包含终点 |
|---|---|---|---|
[start, end] | 闭区间 | 是 | 是 |
[start, end) | 左闭右开 | 是 | 否 |
(start, end) | 开区间 | 否 | 否 |
我推荐绝大多数场景用[start, end)，也就是左闭右开。理由有三条，都是实打实踩出来的。
第一条，它能天然避开"最后一秒"的精度问题。假设你存的是DATETIME(3)毫秒精度，想让"5月20日全天"完整覆盖，写<= '2024-05-20 23:59:59'会漏掉23:59:59.500这条记录。你可能想改写成<= '2024-05-20 23:59:59.999'，但字段精度一旦提升到微秒，这个魔数又不够看了。而写成< '2024-05-21 00:00:00'，无论精度怎么变都不会漏。
第二条，连续区间拼接时不会重叠。做日报、月报的时候，相邻两天的区间如果是闭区间，23:59:59这个点会被两天的报表各统计一次，数字对不上。左闭右开则严丝合缝，前一天的终点就是后一天的起点，不重不漏。
第三条，它跟 Java 里java.time的很多 API 语义一致，像LocalDate.plusDays(1).atStartOfDay()这种写法能直接算出上界，代码读起来也顺。
提示：如果业务方明确要求"包含终点"（比如按自然日统计且数据精确到秒），那就退化为闭区间，但务必在接口文档里写明"end 参数含当秒"，让调用方知道边界行为。
1.2 日期字段和日期时间字段，处理逻辑完全不同
数据库里的时间字段大致分三档，写法差别很大。
第一档是纯日期（DATE），只到天。这时候做范围查询其实是字符串或整数比较，BETWEEN '2024-05-01' AND '2024-05-20'是安全的，因为不存在时分秒，端点语义清晰。
第二档是日期时间（DATETIME / TIMESTAMP），精确到秒、毫秒甚至微秒。这才是坑最多的地方，必须用>= 起点 AND < 终点+1天的写法。
第三档是 Unix 时间戳（BIGINT），秒或者毫秒。这类字段看起来最"土"，实际上最好处理：没有格式、没有时区、没有精度歧义，直接比大小。代价是可读性差，你拿1716134400000是看不出这是哪天的，调试时得先转换。
我个人的经验是：日志、埋点、监控这类高频写入且只用于范围过滤的数据，用毫秒时间戳最省心；业务单据用 DATETIME，方便人工查库和导出。混用是最糟糕的，同一个库里两种都有，写查询的人分分钟搞错。
1.3 时区：那个让你"数据凭空少8小时"的隐藏变量
这是最隐蔽的一类 bug，而且往往在测试环境发现不了。
MySQL 的TIMESTAMP类型在存储时会把值转成 UTC，读取时再按会话时区转回来；DATETIME则原样存储，不做任何转换。如果你的 JDBC 连接串里serverTimezone没配对，Java 里2024-05-20 10:00:00写进去、读出来可能变成2024-05-20 02:00:00，整整差 8 小时——跨天查询就此全线崩盘。
排查这类问题有个笨但有效的办法：写一条SELECT NOW(), @@session.time_zone, @@global.time_zone;，看数据库自己认为现在几点，再跟应用服务器的时间对一下。三边对不上，别急着改业务代码，先把时区配置统一了。
我的建议是能不用TIMESTAMP就别用，老老实实DATETIME，时区转换全部放到应用层做。这样数据库的行为是可预测的，出问题时责任边界也清楚。
2. 主流数据库的时间区间写法对照：别把MySQL的习惯带到Oracle
写 SQL 的人如果不是多数据库背景，很容易把一家的语法硬套到另一家。MySQL 的宽容度特别高，很多写法"看起来能跑"，到了 PostgreSQL 或 Oracle 直接报错。这一节把常见写法对照着列一遍。
2.1 MySQL：BETWEEN 能用但要小心隐式转换
最直白的写法：
SELECT id, order_no, created_at FROM t_order WHERE created_at >= '2024-05-20 00:00:00' AND created_at < '2024-05-21 00:00:00';这是我最推荐的形态。created_at字段前面不加任何函数，字符串常量会被 MySQL 隐式转成DATETIME再比较，索引能正常走范围扫描。
要警惕的是BETWEEN。它等价于>= AND <=，两端都闭，用在纯 DATE 字段上没问题，用在 DATETIME 上就要想清楚终点给什么值：
-- 危险：会漏掉 2024-05-20 当天 00:00:00 之后的所有数据 -- 因为两端都是 00:00:00，区间宽度为零 WHERE created_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 00:00:00' -- 可用但不够优雅：依赖秒级精度，毫秒数据会漏 WHERE created_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59'还有一个常见的坏习惯，用DATE_FORMAT把字段截断到天再比较：
-- 别这么写 WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2024-05-20'它在语义上没问题，但DATE_FORMAT(created_at, ...)是个函数表达式，MySQL 无法用它去走created_at上的索引，只能全表扫描。数据量上万还能忍，上千万就是灾难。
2.2 Oracle：TO_DATE、TRUNC 与字符串比较的顺序
Oracle 对类型的要求严格得多，字符串和日期之间不会那么随意地隐式转换。正确姿势是显式转换：
SELECT id, order_no, created_at FROM t_order WHERE created_at >= TO_DATE('2024-05-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND created_at < TO_DATE('2024-05-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS');如果需要按天聚合或者按天做等值判断，Oracle 里有TRUNC：
-- 按天等于，但同样会破坏 created_at 上的普通索引 WHERE TRUNC(created_at) = DATE '2024-05-20'跟 MySQL 一样，TRUNC(created_at)会让常规 B-Tree 索引失效。真要在 Oracle 里做这种查询，标准做法是建函数索引：CREATE INDEX idx_order_trunc_created ON t_order(TRUNC(created_at));。这是一条很实用的经验——当你不得不在字段上套函数时，去建对应的函数索引，而不是每次都全表扫。
另外提醒一句，Oracle 的DATE类型自带时分秒，只是显示时不带，很多人误以为它只存日期。这也是为什么WHERE created_at = DATE '2024-05-20'往往查不到数据的真正原因——它等价于跟2024-05-20 00:00:00比相等。
2.3 参数化查询：把拼接字符串这条路彻底堵死
不管哪家数据库，时间条件都不该用字符串拼接。除了 SQL 注入风险，还有一个纯技术原因：手工拼出来的时间字符串格式五花八门，2024/5/20、2024-05-20、20240520都有人写，一旦跟数据库期望的格式对不上，要么报错，要么被悄悄转成一个错误的值。
Java 里用PreparedStatement或者 MyBatis 的#{}，让驱动去处理类型转换：
// JDBC 原生写法 String sql = "SELECT id, order_no FROM t_order WHERE created_at >= ? AND created_at < ?"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setTimestamp(1, Timestamp.valueOf(start)); ps.setTimestamp(2, Timestamp.valueOf(end)); try (ResultSet rs = ps.executeQuery()) { // 处理结果 } }setTimestamp会带上时区信息交给驱动处理，比你自己拼字符串可靠一个数量级。这个习惯值不值得养成？我用一句话总结：只要参数是用户传进来的，就永远用占位符，没有例外。
3. 索引为什么没生效：时间区间查询的执行计划拆解
同样一条时间范围查询，有人跑 20 毫秒，有人跑 20 秒。差别通常不在 SQL 写得漂不漂亮，而在索引有没有被用上。这一节不讲索引原理教科书，讲怎么看出来它没用上、以及为什么没用上。
3.1 先学会看 EXPLAIN，别靠猜
MySQL 里执行计划就是照妖镜：
EXPLAIN SELECT id, order_no, created_at FROM t_order WHERE created_at >= '2024-05-20 00:00:00' AND created_at < '2024-05-21 00:00:00';重点看三列：
| 列名 | 期望值 | 含义 |
|---|---|---|
type | range或ref | 访问类型，出现ALL就是全表扫 |
key | 索引名，非 NULL | 实际用到的索引 |
rows | 尽量小 | 预估扫描行数 |
如果type是ALL、key是NULL，基本可以断定索引没用上。接下来的任务是找出为什么。
3.2 函数包字段、隐式类型转换、前导通配符，三大元凶
我把这些年遇到的索引失效原因归了归类，时间查询相关的九成出在前两条。
原因一：字段上套了函数。WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2024-05-20'、WHERE YEAR(created_at) = 2024、WHERE DATE_ADD(created_at, INTERVAL 1 DAY) > NOW()，全是这一类。B-Tree 索引是按字段原始值排序的，你把排序依据改了，索引就没法用来定位了。
原因二：隐式类型转换。这条最阴险。假设created_at是字符串类型（有些老系统真这么干），你写WHERE created_at >= 20240520000000（数字），MySQL 会把字段转成数字再比，索引直接废掉。反过来，字段是 DATETIME，你传了个格式不对的字符串，也可能触发转换。
原因三：范围条件太多。联合索引(user_id, created_at)里，如果既对user_id做范围查询、又对created_at做范围查询，索引只能用到第一个范围列。这是 B-Tree 索引的固有特性，不是配置问题。正确做法是让等值条件排在联合索引前面，范围条件放最后。
排查的顺序我一般是这样的：先EXPLAIN确认key是否为空，再看 SQL 里字段有没有被函数包住，再对一遍字段类型和参数类型是否一致，最后看联合索引的列顺序。
3.3 慢查询日志：定位那些你没意识到的区间
有些区间查询问题不在 SQL 本身，而在"区间开得太大"。比如有人把默认查询范围设成了一年，用户打开页面就触发一次全表扫描。这种问题在开发环境数据少时完全看不出来。
打开慢查询日志：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';跑一段时间后用mysqldumpslow或者pt-query-digest汇总，按"总耗时"排序而不是按"单次耗时"排序。你会发现真正拖垮数据库的往往不是某条特别慢的语句，而是某条"中速"语句被调用了十万次。
我在一个订单系统里就这么干过一次：单个时间区间查询耗时 0.8 秒，没超过 1 秒的慢查询阈值，所以一直没被记录。但报表页每刷新一次要塞 20 个这样的查询，累计 16 秒。把它挑出来加索引之后，整体响应从 18 秒降到 1.2 秒。这个案例我一直拿来讲：慢查询日志的阈值别卡得太死，或者干脆按平均耗时排序来找高频中速语句。
4. Java 侧的时间处理：从参数接收到结果序列化的全链路
SQL 只是时间链路的一半。请求参数从 HTTP 进来是字符串，进入 Service 要变成时间对象，传给 DAO 要变成正确的类型，查回来要格式化给前端。任何一环出错，最终用户看到的就是"数据不对"。
4.1 Java 时间类型选型：别再新建 Date 了
老代码里满是java.util.Date和SimpleDateFormat，这套 API 有两个硬伤：
第一，SimpleDateFormat不是线程安全的。它在内部维护了一个可变的Calendar，多线程下会串数据。我见过线上因为共用一个静态SimpleDateFormat实例，导致时间格式化结果随机错乱的案例，排查了两天才定位到。
第二，Date的语义含糊。它既表示日期又表示时刻，getYear()返回的是"年份减 1900"，getMonth()从 0 开始，用一次骂一次。
新代码一律用java.time：
DateTimeFormatter FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); // 用户传 "2024-05-20"，解析成当天起点 LocalDate day = LocalDate.parse("2024-05-20"); LocalDateTime start = day.atStartOfDay(); LocalDateTime end = day.plusDays(1).atStartOfDay(); // 格式化回字符串 String startStr = start.format(FMT);DateTimeFormatter是不可变对象，天生线程安全，可以放心当静态常量用。LocalDate.plusDays(1)计算次日零点，自动处理月末、年末、闰年，比自己写if (month == 12)靠谱得多。
如果参数带时区（跨区域业务），用ZonedDateTime或者OffsetDateTime，明确指定ZoneId.of("Asia/Shanghai")，别依赖系统默认时区。
4.2 MyBatis 与 MyBatis-Plus 里怎么拼时间条件
MyBatis 里最常规的写法是用<if>做动态条件：
<select id="selectByRange" resultType="Order"> SELECT id, order_no, created_at FROM t_order WHERE 1 = 1 <if test="start != null"> AND created_at >= #{start} </if> <if test="end != null"> AND created_at < #{end} </if> </select>注意>=和<在 XML 里要转义成>=和<，或者包在<![CDATA[ ]]>里，否则解析器会报错。
MyBatis-Plus 的QueryWrapper更贴近 Java 思维：
LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>(); wrapper.ge(Order::getCreatedAt, start) .lt(Order::getCreatedAt, end) .orderByDesc(Order::getCreatedAt);ge和lt就是>=和<，跟前面讲的左闭右开语义正好对上。要留意的是lt传进来的end必须已经算好是次日零点，别在业务代码里传个23:59:59然后指望框架帮你补。
顺带提一个高频错误：用 MyBatis 的if test="start != null"判断时，如果参数是字符串而不是时间对象，空字符串""会通过!= null检查，然后被拼进 SQL，变成一个非法的时间常量，直接报错。稳妥的写法是判断类型后再判断内容，或者干脆在 Controller 层用@DateTimeFormat注解把字符串直接绑定成LocalDateTime。
4.3 返回给前端的时间格式：统一在序列化层做
查出来的created_at默认会被序列化成 ISO 格式或者时间戳数组，前端拿到一脸懵。我的做法是在配置里统一指定：
@Bean public Jackson2ObjectMapperBuilderCustomizer jacksonCustomizer() { return builder -> { DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); builder.serializerByType(LocalDateTime.class, new LocalDateTimeSerializer(fmt)); builder.deserializerByType(LocalDateTime.class, new LocalDateTimeDeserializer(fmt)); }; }这样做的好处是入参出参格式一致，前端不需要额外处理。踩过的坑是：一旦全局配了格式，所有接口都得遵守，特殊接口想用别的格式只能单独加注解。所以配置之前最好跟前端对一次，别各写各的。
5. 分页、聚合与统计：时间区间查询的三种进阶形态
基础的等值范围查询写熟了之后，真实的业务需求往往更复杂。这一节挑三个最常见的进阶场景。
5.1 大区间分页：深分页为什么会越来越慢
用户查"最近一年的订单"，还要翻到第 500 页。SQL 大概长这样：
SELECT id, order_no, created_at FROM t_order WHERE created_at >= '2023-05-20' AND created_at < '2024-05-20' ORDER BY created_at DESC LIMIT 10000, 20;LIMIT 10000, 20的含义是：先按顺序取出 10020 行，再丢掉前 10000 行。翻到后面页，扫描量线性增长，速度越来越慢。
改善的办法是游标分页，用上一页最后一条记录的时间作为下一页的起点：
SELECT id, order_no, created_at FROM t_order WHERE created_at >= '2023-05-20' AND created_at < '2024-05-20' AND created_at < '2024-03-15 08:30:12' -- 上一页最后一条的时间 ORDER BY created_at DESC LIMIT 20;这样每次查询都只扫 20 行，跟页码无关。代价是不能跳页，只能上一页下一页。订单流水、操作日志这种"按时间倒序浏览"的场景，游标分页完美适配。但如果是"跳到第 87 页看某个具体订单"这种需求，游标分页就不合适了，得换个思路。
还有一个小窍门：如果时间字段上有索引且区分度高，可以考虑把ORDER BY created_at换成ORDER BY id（前提是主键自增且与时间正相关），MySQL 优化器有时能因此选择更优的路径。
5.2 按天聚合：时间桶怎么切才不会缺数据
报表需求经常是"按天统计订单量"：
SELECT DATE(created_at) AS day, COUNT(*) AS cnt FROM t_order WHERE created_at >= '2024-05-01' AND created_at < '2024-06-01' GROUP BY DATE(created_at) ORDER BY day;这段 SQL 本身没问题，但有两个隐忧。
其一，DATE(created_at)无法用索引，聚合必然全扫。如果表很大而查询频率又高，正确做法是建一张按天预聚合的统计表，定时任务每天凌晨跑一次，报表直接读结果。用空间换时间，这是报表场景的通行做法。
其二，没有数据的那一天不会出现在结果里。5月1日到5月31日，如果5月10日零订单，结果集里就没有 5 月 10 日这一行。前端画折线图时，这一天会被直接跳过，看起来像是"5月9日直接连到5月11日"。解决办法是在应用层补全日期序列，查出来的结果作为 Map，再按完整日期列表去填，缺的补 0。
Map<LocalDate, Long> cntMap = result.stream() .collect(Collectors.toMap(DayCount::getDay, DayCount::getCnt)); List<DayCount> full = new ArrayList<>(); for (LocalDate d = startDate; d.isBefore(endDate); d = d.plusDays(1)) { full.add(new DayCount(d, cntMap.getOrDefault(d, 0L))); }这个"补零"动作看起来琐碎，但缺了它报表就是错的。
5.3 子查询与更新语句里的时间条件
有一种需求是"把最近 7 天的订单标记为待归档"。MySQL 里直接写：
UPDATE t_order SET status = 'ARCHIVED' WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND status = 'DONE';看着挺正常，但如果你写成下面这样就要小心了：
-- 谨慎：更新和子查询用的是同一张表 UPDATE t_order SET status = 'ARCHIVED' WHERE id IN ( SELECT id FROM t_order WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) );MySQL 不允许在UPDATE的子查询里直接引用被更新的同一张表，会报You can't specify target table for update in FROM clause。绕过的办法是套一层派生表：
UPDATE t_order SET status = 'ARCHIVED' WHERE id IN ( SELECT id FROM ( SELECT id FROM t_order WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ) AS tmp );这里的tmp是必须的，很多人生成 SQL 时把它漏掉，然后对着报错发懵。原理是派生表会被物化成临时表，从而切断"同一张表"的直接引用关系。
批量归档这类操作还要注意事务大小。一次更新几十万行会长时间持有锁，阻塞正常读写。稳妥做法是分批发：每次取 1000 条的时间上界作为下批的起点，循环执行，中间留一点间隔。
6. 一套可复用的排查清单：下次再遇到时间查询问题直接照着走
前面五节讲的是原理和方法，这一节把它压缩成一份能贴在显示器旁边的清单。我按"发现问题"到"验证修复"的顺序排，遇到问题从头往下走就行。
6.1 五步定位法
第一步，确认区间语义。业务方要的是"某一天"还是"某个精确时刻到某个精确时刻"？起点和终点是否包含？把答案写成一句话，再翻译成 SQL。
第二步，把时间范围打印出来。在 Service 层加一行日志，输出实际传给 DAO 的start和end。我遇到过太多次"代码写的是对的，但传进来的参数是错的"，比如前端传了个空字符串被解析成了 1970 年。
第三步，把 SQL 拿到数据库里手工跑。用打印出来的具体值替换占位符，在客户端执行。这一步能立刻区分"SQL 逻辑问题"和"应用层参数问题"。
第四步，EXPLAIN 看执行计划。key为空、type为ALL，说明索引没用上，回到 3.2 节找原因。
第五步，对比修复前后耗时。别只看"感觉快了"，用真实数据量测。测试环境数据少时索引用不用得上差别不大，这种"假阳性"最坑人。
6.2 常见问题对照表
| 现象 | 最可能的原因 | 处理方向 |
|---|---|---|
| 查某天数据为空 | 区间起止相同或终点取 00:00:00 | 终点改为次日零点，用< |
| 少了最后几毫秒的数据 | 终点写成23:59:59 | 改为次日零点并用< |
| 相邻两天数据重复 | 用了闭区间拼接 | 统一改成左闭右开 |
| 数据整体偏移 8 小时 | 时区配置不一致 | 检查serverTimezone与服务器时区 |
| 查询慢、EXPLAIN 显示全表扫 | 字段套了函数或类型不匹配 | 去掉函数、对齐类型 |
| 联合索引只用到前半段 | 范围条件排在等值条件之前 | 调整索引列顺序 |
| 翻页越翻越慢 | 深分页LIMIT offset | 改游标分页或按主键过滤 |
| 更新语句报子查询错误 | 子查询引用了被更新表 | 套一层派生表 |
6.3 几个容易忽略的实操细节
最后一节分享几个小技巧，都是文档里不太写、但实际很好用的。
用半开区间做"昨天"的通用公式。无论什么数据库，昨天 = [今天零点减一天, 今天零点)。Java 里就是LocalDate.now().minusDays(1).atStartOfDay()和LocalDate.now().atStartOfDay()，端点全靠plusDays/minusDays推，绝不手写时分秒。
给时间字段加索引时要考虑查询模式。如果经常是"按用户+时间段查"，就建(user_id, created_at)；如果经常是"全表按时间段扫"，单独建(created_at)即可。索引不是越多越好，每个索引都会拖慢写入，一张表上超过五六个索引就该重新审视了。
测试用例一定要覆盖边界。数据里至少埋三条：区间起点前 1 毫秒、正好等于起点、正好等于终点。跑一遍如果三条都符合预期，这段代码基本就稳了。我所在的团队把这三条做成了单测模板，新写的查询方法直接套，省了无数回归时间。
别在生产库上直接试 SQL。大区间查询可能瞬间把 CPU 拉满。要试就在从库或者测试库试，并且先看一眼EXPLAIN里的rows预估，几十万行以上就别贸然执行了。
慢查询日志记得定期分析，别只开不看。开了阈值不等于问题解决了，每周花半小时用pt-query-digest过一遍，把 Top 10 里跟时间查询相关的挑出来优化，比事后救火轻松太多。
时间字段的查询说简单也简单，一行WHERE就完事；说复杂也复杂，从区间语义到索引到序列化一路都是能翻车的地方。我自己的体会是：凡是跟时间相关的代码，都按"最坏情况会发生"来写，边界多想一步，类型多确认一次，日志多打一行。这几年的线上事故里，真正因为算法复杂、架构精妙而出的问题其实很少，绝大多数都是这种看起来毫不起眼的边界细节。下次你写时间范围查询时，不妨把区间先写成[start, end)，再回头看看是不是少了很多麻烦。
