BigQuery正则校验日期格式异常：NOT逻辑返回符合格式的日期

可能的原因 #

• 隐形空白/不可见字符：用CAST(logged_in_at AS STRING)转换时，字符串可能带有前导、后导空格，或是制表符、换行符这类不可见字符。你的正则严格匹配开头^和结尾$，这些额外字符会导致匹配失败，进而被NOT条件筛选出来。
• 时区字符串大小写差异：如果部分记录的时区是小写utc，而你的正则仅匹配大写UTC，也会导致符合格式的记录被误筛选。
• TIMESTAMP转STRING的格式细节：BigQuery在某些场景下的字符串输出可能存在细微差异，比如毫秒部分的补零逻辑或格式变种，不过从你的示例来看这个可能性较低。

解决办法 #

1. 处理隐形字符

可以修改正则允许前后空白，或者先清理字符串：

-- 方法1：正则兼容前后空白
SELECT *
FROM db_logs.FACT_LOG_WEBSITE
WHERE NOT REGEXP_CONTAINS(CAST(logged_in_at AS STRING), r'^\s*\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6} UTC\s*$')

-- 方法2：用TRIM清理字符串后再匹配
SELECT *
FROM db_logs.FACT_LOG_WEBSITE
WHERE NOT REGEXP_CONTAINS(TRIM(CAST(logged_in_at AS STRING)), r'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6} UTC$')

2. 兼容时区大小写

使用RE2正则的不区分大小写修饰符(?i)：

SELECT *
FROM db_logs.FACT_LOG_WEBSITE
WHERE NOT REGEXP_CONTAINS(CAST(logged_in_at AS STRING), r'(?i)^\s*\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{6} utc\s*$')

3. 更可靠的类型校验（推荐）

放弃正则，直接用BigQuery的SAFE_CAST函数判断是否能转成合法TIMESTAMP，返回NULL的就是格式错误的记录：

SELECT *
FROM db_logs.FACT_LOG_WEBSITE
WHERE SAFE_CAST(logged_in_at AS TIMESTAMP) IS NULL

这种方法利用数据库原生的类型校验逻辑，能覆盖正则可能遗漏的所有非法格式，准确性更高。

内容的提问来源于stack exchange，提问作者Valerio Auricchio