如何处理BigQuery中超出范围的TIMESTAMP值？排查过滤异常行

一、检测异常行 #

1. 找出无法转换为INT64的ts值 #

如果ts字段是字符串类型，部分值可能无法转换为整数，执行以下查询定位这类异常：

SELECT ts
FROM your_table
WHERE SAFE_CAST(ts AS INT64) IS NULL;

2. 找出超出TIMESTAMP范围的毫秒值 #

BigQuery的TIMESTAMP允许范围对应Unix毫秒数为 -62135596800000（对应0001-01-01）到 253402300799999（对应9999-12-31）。执行以下查询定位超出该范围的异常行：

SELECT ts
FROM your_table
WHERE
  SAFE_CAST(ts AS INT64) IS NOT NULL
  AND (SAFE_CAST(ts AS INT64) < -62135596800000 OR SAFE_CAST(ts AS INT64) > 253402300799999);

二、过滤异常行并计算会话时长 #

方法1：先过滤异常行再计算 #

直接在WHERE子句中排除无效值，确保后续计算仅处理合法时间戳：

SELECT
  -- 如果是按分组计算，需添加GROUP BY子句，比如GROUP BY session_id
  TIMESTAMP_DIFF(
    MAX(TIMESTAMP_MILLIS(SAFE_CAST(ts AS INT64))),
    MIN(TIMESTAMP_MILLIS(SAFE_CAST(ts AS INT64))),
    SECOND
  ) AS session_length_in_seconds
FROM your_table
WHERE
  SAFE_CAST(ts AS INT64) IS NOT NULL
  AND SAFE_CAST(ts AS INT64) BETWEEN -62135596800000 AND 253402300799999;

方法2：使用SAFE函数自动忽略无效值 #

借助SAFE.TIMESTAMP_MILLIS函数，转换无效时间戳时返回NULL，MAX/MIN函数会自动忽略NULL值，无需额外过滤：

SELECT
  -- 按分组计算时添加GROUP BY子句
  TIMESTAMP_DIFF(
    MAX(SAFE.TIMESTAMP_MILLIS(SAFE_CAST(ts AS INT64))),
    MIN(SAFE.TIMESTAMP_MILLIS(SAFE_CAST(ts AS INT64))),
    SECOND
  ) AS session_length_in_seconds
FROM your_table;

内容的提问来源于stack exchange，提问作者Simon Breton