4、时间戳处理：时间格式转换、时间差计算、时间窗口定义

说起日志里的时间戳，我估计不少同学都踩过坑。各家系统打出来的时间格式五花八门，有的用 Unix 毫秒，有的用 ISO 8601，还有一些干脆是「2024-01-15 14:23:07.123」这种自定义格式。做关联分析的时候，时间字段对不上，那基本就废了。

这一章我重点讲三个东西：怎么把乱七八糟的时间格式统一起来，怎么算两个时间点的差值，还有怎么灵活地定义时间窗口。掌握了这些，你写日志分析的 SQL 会顺手很多。

核心原则：日志关联分析中的时间字段，必须在同一个时区、同一个精度下进行运算。否则你以为的「5分钟窗口」，实际上可能差了8小时。

4.1 时间格式转换——先把「方言」翻译成「普通话」

我在项目里见过最头疼的情况：同一个链路里，上游系统用 unix_timestamp（秒级），下游用 datetime 字符串，再下游又用了 timestamp with time zone。要把它们关联起来，第一步就是统一格式。

常用的转换函数，我列个表给你参考：

原始格式 目标格式 SQL 函数示例（MySQL） 说人话
Unix 秒数 DATETIME FROM_UNIXTIME(ts) 数字变日期
Unix 毫秒 DATETIME FROM_UNIXTIME(ts / 1000) 先除1000再转
字符串 '2024-01-15 14:23:07' Unix 秒 UNIX_TIMESTAMP(str) 日期变数字
字符串含微秒 DATETIME(6) STR_TO_DATE(str, '%Y-%m-%d %H:%i:%s.%f') 模板匹配
ISO 8601 UTC 时间戳 CONVERT_TZ(str, '时区', '+00:00') 时区强制对齐

我个人的习惯：在数据仓库里统一存成 DATETIME(3) 毫秒精度的 UTC 时间。关联的时候直接用 BETWEEN 或者 JOIN 条件，不用再反复转换，省心不少。

举个实际例子。假设订单日志的 create_time 是字符串 '2024-06-12T08:30:45.123Z'，支付日志的 pay_ts 是 Unix 毫秒 1718166645123。你想找出订单创建后 5 分钟内有没有支付记录，就得先统一格式：

-- 订单时间转 Unix 毫秒
SELECT 
    order_id,
    UNIX_TIMESTAMP(STR_TO_DATE(
        REPLACE(create_time, 'T', ' '), 
        '%Y-%m-%d %H:%i:%s.%f'
    )) * 1000 AS order_ms_utc
FROM orders;

嗯，这里要注意：REPLACE 把 ISO 里的 'T' 换成空格，才能用 STR_TO_DATE 解析。我刚开始用的时候直接拿 'T' 去匹配，结果解析失败，浪费了半小时。

4.2 时间差计算——别被「毫秒 vs 秒」坑了

时间差计算的核心就三个函数：TIMESTAMPDIFF（MySQL）、DATEDIFF（日期差）、TIMEDIFF（时间差）。但千万注意单位！

我曾经在线上排查一个延迟问题，看到 TIMESTAMPDIFF(MINUTE, start, end) 算出来是 1，以为正常。后来才发现 end 是毫秒级别的时间戳，start 是秒级，差了一千倍。结果实际延迟是 16 分钟。嗯，这种低级错误，谁还没犯过呢。

下面是我总结的标准做法：

  1. 先统一精度：所有时间戳转成相同单位（我推荐毫秒，因为毫秒是多数业务日志的精度）。
  2. 再用 FLOOR 或 ROUND：计算差值时根据需要取舍到秒或分钟。
  3. 避免直接相减：除非你能确定两个字段是同一个函数生成的 Unix 时间。
-- 安全的时间差计算（毫秒级）
SELECT 
    a.log_id,
    b.log_id,
    (a.event_time_ms - b.event_time_ms) AS diff_ms,
    -- 转成秒
    (a.event_time_ms - b.event_time_ms) / 1000 AS diff_sec,
    -- 转成分
    FLOOR((a.event_time_ms - b.event_time_ms) / 60000) AS diff_min
FROM logs a
JOIN logs b ON a.trace_id = b.trace_id
WHERE a.event_type = 'request'
  AND b.event_type = 'response';

小心时区隐式转换：MySQL 的 TIMESTAMPDIFF 如果传入的是 DATETIME，会默认使用会话时区。如果你的服务器时区是 +08:00，而日志存的是 UTC，那算出来的差值直接多 8 小时。我建议所有时间字段显式指定时区，或者在查询前用 SET time_zone = '+00:00'; 先对齐。

4.3 时间窗口定义——滑动、固定、还是会话？

时间窗口是日志关联分析的灵魂。说白了，你要回答「A 事件发生后 N 分钟内，有没有出现 B 事件？」这个问题。

我一般把时间窗口分成三类：

  • 固定窗口：比如每天 00:00-23:59，或者每小时整点切片。适合统计类场景。
  • 滑动窗口：以某个事件的时间为起点，往后推 N 秒/分/时。适合关联分析。
  • 会话窗口：连续事件之间的间隔不超过某个阈值，否则算新会话。适合用户行为分析。

你看，这三种窗口在 SQL 里的实现方式完全不同。我这里重点讲滑动窗口，因为日志关联分析里它最常用。

-- 滑动窗口示例：查询每个 request 后 5 秒内的 response
SELECT 
    r.log_id AS request_id,
    p.log_id AS response_id,
    r.event_time,
    p.event_time,
    TIMESTAMPDIFF(SECOND, r.event_time, p.event_time) AS latency_sec
FROM logs r
JOIN logs p 
  ON r.trace_id = p.trace_id
 AND p.event_type = 'response'
 AND p.event_time >= r.event_time
 AND p.event_time <= r.event_time + INTERVAL 5 SECOND;

这里 INTERVAL 5 SECOND 就是窗口宽度。你想想看，如果日志里同一 trace 出现多个 response，这个查询会把所有在窗口内的都匹配出来。有时候我们需要取最近的一个，那就加个 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY p.event_time) 再过滤。

固定窗口的写法更直白：

-- 固定窗口：每 10 分钟一个桶
SELECT 
    DATE_FORMAT(event_time, '%Y-%m-%d %H:') AS hour_prefix,
    FLOOR(MINUTE(event_time) / 10) AS bucket_id,
    COUNT(*) AS cnt
FROM logs
GROUP BY hour_prefix, bucket_id;

至于会话窗口，市面上多数 SQL 引擎都提供了 SESSION 窗口函数。比如 Flink SQL 里用 SESSION(ts, INTERVAL '5' MINUTE)，MySQL 的话只能自己写个累积逻辑，稍微麻烦一点。

我的经验：如果你用的是传统 OLTP 数据库（MySQL、PostgreSQL），尽量用 JOIN + BETWEEN 实现滑动窗口，不要试图写复杂的子查询或变量。性能虽然未必最优，但可读性好，出问题也好排查。

4.4 一个完整的实战案例

假设我们要分析：每条订单支付成功后，3 分钟内是否收到了物流发货通知。日志表结构如下：

字段 类型 说明
event_id BIGINT 主键
order_id VARCHAR(64) 订单号
event_type VARCHAR(32) 'pay' 或 'ship'
event_time DATETIME(3) 事件发生时间（UTC） 
-- 找出 3 分钟内发货失败的订单
WITH pay_events AS (
    SELECT 
        order_id,
        event_time AS pay_time
    FROM logs
    WHERE event_type = 'pay'
),
ship_events AS (
    SELECT 
        order_id,
        event_time AS ship_time
    FROM logs
    WHERE event_type = 'ship'
)
SELECT 
    p.order_id,
    p.pay_time,
    MIN(s.ship_time) AS first_ship_time,
    TIMESTAMPDIFF(SECOND, p.pay_time, MIN(s.ship_time)) AS gap_seconds,
    CASE 
        WHEN MIN(s.ship_time) IS NULL THEN '未发货'
        WHEN TIMESTAMPDIFF(MINUTE, p.pay_time, MIN(s.ship_time)) <= 3 THEN '合规'
        ELSE '超时'
    END AS status
FROM pay_events p
LEFT JOIN ship_events s 
  ON p.order_id = s.order_id
 AND s.ship_time >= p.pay_time
 AND s.ship_time <= p.pay_time + INTERVAL 3 MINUTE
GROUP BY p.order_id, p.pay_time;

这个查询用 LEFT JOIN + 滑动窗口条件，保证即使没有发货记录也能返回订单（状态为「未发货」）。我当年排查一个线上漏单问题时，用的就是类似的思路：用 LEFT JOIN 而不是 INNER JOIN，否则那些异常数据就被默默吞掉了。

时间戳处理核心流程 原始时间戳 统一格式（UTC+毫秒） 时间差计算 时间窗口 精准关联分析 ⚠️ 常见陷阱 时区不一致 / 毫秒与秒混淆

这张图概括了时间戳处理的三个步骤：先转换格式，再计算差值，最后定义窗口做关联。每一步都有坑，图里左下角标了两个最常见的。

一个小技巧：写时间窗口的 SQL 之前，先拿一条记录手动算一下期望值，然后用 SELECT 不带 JOIN 测一下转换和差值是否正确。这样可以避免被复杂的关联条件带偏。

4.5 避坑清单（我踩过的雷）

  • Unix 时间戳的单位：UNIX_TIMESTAMP() 返回秒，FROM_UNIXTIME() 也接受秒。但很多第三方 SDK 打的是毫秒，不除以 1000 直接传进去，得出的时间要么是 1970 年，要么是天书。
  • 时区转换遗忘：日志采集服务器可能在 UTC，但业务库是北京时间。关联的时候如果只截取日期，就会导致「今天」的数据少 8 小时。我建议所有时间字段入库前就转成 UTC，展示层再转换。
  • 窗口边界措辞：用 >= 还是 > ？我的习惯是左闭右开：event_time >= start AND event_time < start + INTERVAL N，这样可以避免同一个事件被两个窗口重复计入。
  • 性能陷阱：在 ON 条件里用 event_time + INTERVAL 5 SECOND 会导致无法使用索引。更好的做法是先算好窗口的右边界，存成一个字段，然后直接 <= 比较。

嗯，这一章内容不少。你只要记住一条：**时间戳处理的核心不是函数调用，而是对单位、时区、边界的一致性管理**。函数只是个工具，脑中的对齐意识才是关键。

← 上一章 📖 返回目录 下一章 →