如何在Athena中校验timestamp是否符合yyyy-MM-dd HH:mm:ss.SSS格式

首先要明确：Athena中的timestamp类型是二进制存储的，本身不存在字符串格式的概念，你观察到的不同格式是查询结果输出时的字符串序列化格式。如果你要校验的是原始存储的字符串时间字段是否符合yyyy-MM-dd HH:mm:ss.SSS格式，或是timestamp类型序列化后是否匹配该格式，可以用以下几种实现方案：

• 方案1：针对字符串类型的原始时间字段校验
  直接用date_parse函数指定目标格式尝试解析，解析失败会返回null，可同时校验格式规则和日期逻辑合法性：

  SELECT 
    your_time_str_col,
    CASE 
      WHEN date_parse(your_time_str_col, '%Y-%m-%d %H:%i:%s.%f') IS NOT NULL THEN '符合格式'
      ELSE '不符合格式'
    END AS format_check_result
  FROM your_table
  

  格式符说明：%f对应3位毫秒的匹配，刚好符合你要求的SSS部分规则。如果需要直接过滤掉不符合格式的行，在WHERE条件中加date_parse(your_time_str_col, '%Y-%m-%d %H:%i:%s.%f') IS NOT NULL即可。

• 方案2：针对已为timestamp类型的字段，校验其序列化输出格式是否符合要求
  先把timestamp用date_format转成目标格式的字符串，再反向解析后和原timestamp对比，如果一致说明符合格式要求：

  SELECT 
    your_timestamp_col,
    CASE 
      WHEN date_parse(date_format(your_timestamp_col, '%Y-%m-%d %H:%i:%s.%f'), '%Y-%m-%d %H:%i:%s.%f') = your_timestamp_col THEN '符合格式'
      ELSE '不符合格式'
    END AS format_check_result
  FROM your_table
  

  如果你的timestamp字段本身存储了时区信息、或者默认带T分隔符的ISO序列化属性，转换后反向解析的结果就会和原值不一致，可以直接识别出来。

• 方案3：正则匹配快速校验（适合字符串类型字段前置筛查）
  用正则表达式严格匹配目标格式的字符规则，适合大批量数据的前置快速筛查：

  SELECT 
    your_time_str_col,
    CASE 
      WHEN regexp_like(your_time_str_col, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3}$') THEN '符合格式'
      ELSE '不符合格式'
    END AS format_check_result
  FROM your_table
  

  注意：该方法只会校验格式的字符规则，不会校验日期逻辑合法性（比如不会识别2023-13-01 12:00:00.000这种月份非法的值），适合和date_parse搭配使用提升校验准确性。

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