如何处理非标准JSON日期？SQL及解析方案求助

首先得说，你遇到的这种带空格和点的12小时制日期格式确实挺坑，但主流的JSON解析器大多支持自定义格式字符串来适配，这绝对是优先推荐的方案：

一、标准解析器的自定义格式字符串 #

1. .NET（Newtonsoft.Json） #

如果你用的是Newtonsoft.Json，这个格式字符串刚好能匹配你的异常日期：

"yyyy-MM-ddTH:mm:ss.fffffff tt\\.m\\.Z"

拆解一下逻辑：

• yyyy-MM-dd 对应日期部分
• TH:mm:ss.fffffff 匹配时间（H用来兼容单数字的12小时制小时）
• tt\\.m\\. 专门适配 p. m./ a. m.的格式（点号需要转义）
• Z 匹配末尾的UTC时区标记

配置到序列化器里的示例代码：

var settings = new JsonSerializerSettings
{
    DateFormatString = "yyyy-MM-ddTH:mm:ss.fffffff tt\\.m\\.Z",
    DateTimeZoneHandling = DateTimeZoneHandling.Utc
};
var targetObj = JsonConvert.DeserializeObject<YourDataModel>(jsonContent, settings);

2. Java（Jackson） #

Java 8+用DateTimeFormatter可以这么写，还能同时兼容AM和PM的情况：

DateTimeFormatter customFormatter = DateTimeFormatter.ofPattern(
    "yyyy-MM-dd'T'h:mm:ss.SSSSSSS ['a. m.'|'p. m.']Z"
);

配置到Jackson的ObjectMapper里：

ObjectMapper mapper = new ObjectMapper();
mapper.registerModule(new JavaTimeModule());
mapper.setDateFormat(new DateTimeFormatterBuilder()
    .appendPattern("yyyy-MM-dd'T'h:mm:ss.SSSSSSS")
    .appendLiteral(" ")
    .appendOptional(DateTimeFormatter.ofPattern("a. m."))
    .appendOptional(DateTimeFormatter.ofPattern("p. m."))
    .appendLiteral("Z")
    .toFormatter());

二、SQL更新脚本方案 #

如果没法修改解析器配置，直接在数据库里批量转换也是可行的，这里给两个主流数据库的示例：

MySQL #

UPDATE your_target_table
SET date_column = STR_TO_DATE(
    REPLACE(REPLACE(date_column, ' p. m.', ' PM'), ' a. m.', ' AM'),
    '%Y-%m-%d%l:%i:%s.%f %pZ'
)
WHERE date_column LIKE '%p. m.%' OR date_column LIKE '%a. m.%';

先把带点的AM/PM标记替换成标准的AM/PM，再用STR_TO_DATE识别转换。

PostgreSQL #

UPDATE your_target_table
SET date_column = TO_TIMESTAMP(
    REPLACE(REPLACE(date_column, ' p. m.', ' PM'), ' a. m.', ' AM'),
    'YYYY-MM-DDHH:MI:SS.US TZ'
)
WHERE date_column LIKE '%p. m.%' OR date_column LIKE '%a. m.%';

三、手动字符串解析（备选） #

万不得已才用这个，比如Python的示例：

import datetime

def convert_custom_date(date_str):
    # 替换成标准AM/PM标记
    normalized_str = date_str.replace('p. m.', 'PM').replace('a. m.', 'AM')
    # 解析成datetime对象
    parsed_dt = datetime.datetime.strptime(normalized_str, '%Y-%m-%d%I:%M:%S.%f%pZ')
    # 转成你需要的标准格式
    return parsed_dt.strftime('%Y-%m-%dT%H:%M:%S.%fZ')

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