本文介绍在计算用户账户启用时长时，如何通过 sql 函数灵活处理起止时间为空或越界的情况，确保跨时段、持续启用、部分重叠等边界场景被准确计入总秒数。
本文介绍在计算用户账户启用时长时，如何通过 sql 函数灵活处理起止时间为空或越界的情况，确保跨时段、持续启用、部分重叠等边界场景被准确计入总秒数。
在实际业务中（如统计用户账号在指定 7 天窗口内的有效启用时长），原始时间范围查询常因开区间语义而漏算关键数据：
- ✅ 账户在统计周期开始后启用（enabled_start > 统计起始时间），但应从统计起始时间起计；
- ✅ 账户至今仍启用（enabled_end IS NULL），应计至统计结束时间；
- ⚠️ 原始 WHERE enabled_start >= ? AND enabled_end <= ? 条件会直接排除上述两类记录，导致结果严重偏低。
解决核心在于：将逻辑从“完全落在区间内”升级为“与区间有交集”，并对交集的起止点做动态截断。
✅ 正确做法：用 GREATEST 和 LEAST 动态裁剪时间交集
我们不再依赖 WHERE 过滤整条记录，而是对每条记录的启用区间与目标统计窗口求交集，并计算交集长度：
SELECT SEC_TO_TIME(
SUM(
TIME_TO_SEC(
TIMEDIFF(
LEAST(COALESCE(enabled_end, '2022-04-27 23:59:59'), '2022-04-27 23:59:59'),
GREATEST(enabled_start, '2022-04-22 00:00:00')
)
)
)
) AS total_seconds
FROM logs
WHERE user_id = '123'
AND enabled_start <= '2022-04-27 23:59:59' -- 至少在窗口内开始
AND (enabled_end IS NULL OR enabled_end >= '2022-04-22 00:00:00'); -- 或仍在窗口内结束/未结束关键函数解析：
- COALESCE(enabled_end, '2022-04-27 23:59:59')：将 NULL 替换为统计截止时间，表示“无限延续”；
- LEAST(..., '2022-04-27 23:59:59')：确保交集终点不超出统计窗口；
- GREATEST(enabled_start, '2022-04-22 00:00:00')：确保交集起点不早于统计窗口；
- WHERE 条件改为 “有交集”逻辑（即：启用开始 ≤ 窗口结束 且 启用结束（或无限）≥ 窗口开始），避免漏行。
验证示例数据（user_id = 123，窗口：2022-04-22 00:00:00 → 2022-04-27 23:59:59）：
| id | enabled_start | enabled_end | 交集起点 | 交集终点 | 时长（秒） |
|---|---|---|---|---|---|
| 5 | 2022-04-26 12:13:38 | NULL | 2022-04-26 12:13:38 | 2022-04-27 23:59:59 | ✅ 全部计入 |
| 4 | 2022-04-25 15:22:36 | 2022-04-25 17:32:11 | 同上 | 同上 | ✅ 完全包含 |
| 2 | 2022-04-15 14:44:00 | 2022-04-23 10:58:53 | 2022-04-22 00:00:00 | 2022-04-23 10:58:53 | ✅ 起点前移 |
? 注意事项：
- 时间字面量建议使用标准格式 'YYYY-MM-DD HH:MM:SS'，避免隐式类型转换风险；
- 若数据库时区与业务时区不一致，请统一使用 CONVERT_TZ() 或在应用层对齐；
- 对于高频查询，可在 (user_id, enabled_start, enabled_end) 上建立复合索引提升性能；
- TIMEDIFF(a,b) 要求 a >= b，否则返回负值——本方案中 LEAST(...) >= GREATEST(...) 恒成立，无需额外校验。
此方法本质是将「开区间交集计算」转化为安全的数值裁剪，兼顾准确性、可读性与兼容性，适用于 MySQL 5.7+ 及多数主流 SQL 数据库（PostgreSQL/SQL Server 可用对应函数如 LEAST/GREATEST 或 CASE WHEN 替代）。
