如何检查BigQuery表中TIMESTAMP类型列的无效值？

在BigQuery中，TIMESTAMP类型仅支持存储合法的时间戳值，空字符串、空格、普通文本等无效内容无法直接存入该类型列——尝试插入这类值时，要么触发报错，要么通过SAFE_CAST等转换工具转为NULL。因此检查这类无效值，核心是排查NULL值及潜在的异常转换情况，具体操作如下：

1. 统计无效值数量 #

查询TIMESTAMP列中NULL值的数量及占比，这些NULL对应无法转换为合法时间戳的无效输入：

SELECT
  COUNT(*) AS invalid_timestamp_count,
  ROUND((COUNT(*) / (SELECT COUNT(*) FROM `your-project.your-dataset.your-table`)) * 100, 2) AS invalid_percentage
FROM `your-project.your-dataset.your-table`
WHERE time_created IS NULL;

2. 排查原始无效输入（若有对应字符串列） #

如果表中保留了用于转换为TIMESTAMP的原始字符串列（例如time_created_raw），可以直接查询具体的无效原始值：

SELECT DISTINCT time_created_raw AS invalid_raw_values
FROM `your-project.your-dataset.your-table`
WHERE time_created IS NULL;

3. 验证异常时间戳格式 #

若没有原始字符串列，可将TIMESTAMP转回字符串，检查是否存在不符合标准格式的异常值：

SELECT
  time_created,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', time_created) AS formatted_timestamp
FROM `your-project.your-dataset.your-table`
-- 筛选长度异常或包含非标准字符的结果
WHERE LENGTH(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', time_created)) != 19
   OR REGEXP_CONTAINS(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', time_created), r'[^0-9\- :]');

注意事项 #

• 若数据导入时未使用SAFE_CAST，无效字符串会直接导致导入失败，此时需查看导入日志定位问题。
• 如需保留原始时间输入，建议单独设置STRING类型列存储，避免直接将无效内容存入TIMESTAMP列。

内容的提问来源于stack exchange，提问作者Firdosh Alia