Spark：将列中任意格式的时间戳转换为指定标准格式

Python 实现 #

针对不确定格式的时间戳，可通过遍历预设格式列表尝试解析，再转换为目标格式：

from datetime import datetime

def standardize_timestamp(input_ts):
    # 列出所有可能的输入格式，可根据实际场景补充
    candidate_formats = [
        "%d-%m-%Y %H:%M:%S.%f",
        "%Y/%m/%d %H:%M:%S.%f",
        "%d/%m/%Y %H:%M:%S.%f",
        "%Y-%m-%d %H:%M:%S.%f"
    ]
    
    for fmt in candidate_formats:
        try:
            dt_obj = datetime.strptime(input_ts, fmt)
            # 转换为目标格式，保留3位毫秒
            return dt_obj.strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
        except ValueError:
            continue
    # 所有格式匹配失败时抛出异常，或根据需求返回原字符串
    raise ValueError(f"Unsupported timestamp format: {input_ts}")

# 测试用例
print(standardize_timestamp("05-03-2024 09:15:30.456"))  # 输出 2024-03-05 09:15:30.456
print(standardize_timestamp("2024/03/05 09:15:30.456"))  # 输出 2024-03-05 09:15:30.456

说明：如果有其他输入格式，直接添加到candidate_formats列表即可。转换后的时间戳既可以用字符串做区间比较，也可以保留datetime对象进行更精准的时间运算。

SQL 实现（以MySQL为例） #

利用STR_TO_DATE尝试匹配不同格式，再通过DATE_FORMAT转换为目标格式：

SELECT 
    CASE
        WHEN STR_TO_DATE(ts_col, '%d-%m-%Y %H:%i:%s.%f') IS NOT NULL 
            THEN LEFT(DATE_FORMAT(STR_TO_DATE(ts_col, '%d-%m-%Y %H:%i:%s.%f'), '%Y-%m-%d %H:%i:%s.%f'), 23)
        WHEN STR_TO_DATE(ts_col, '%Y/%m/%d %H:%i:%s.%f') IS NOT NULL 
            THEN LEFT(DATE_FORMAT(STR_TO_DATE(ts_col, '%Y/%m/%d %H:%i:%s.%f'), '%Y-%m-%d %H:%i:%s.%f'), 23)
        -- 可继续添加更多格式判断分支
        ELSE ts_col  -- 无法解析时返回原值，或自定义处理逻辑
    END AS standardized_timestamp
FROM your_table;

说明：MySQL的%f会解析微秒（6位），用LEFT(..., 23)截取到3位毫秒，符合目标格式要求。转换后即可直接用于时间区间比较。

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