Postgres：SMTP格式时间戳存在排序异常问题

这问题我之前做邮件相关项目时碰到过，太懂这种踩坑的感觉了！出现负时间间隔、排序混乱的核心原因，基本都是把字符串当时间来排序，再加上SMTP时间格式本身的几个小坑，才搞出了异常。

异常原因拆解 #

• 字符串字典序≠时间顺序：SMTP时间是字符串格式（比如Fri, 18 Aug 17 14:15:26 UTC），如果直接对这个字符串字段排序，程序/数据库会按字符的ASCII码顺序比较，而不是时间先后。比如Wed, 20 Sep 16 10:00:00 UTC（2016年）的字符串开头是'W'，比Fri, 18 Aug 17...（2017年）的'F'ASCII码大，排序时会被放到后面，但实际2016年的时间更早，这就导致排序颠倒，计算时间间隔时自然会出现负数。
• 两位年份的歧义：SMTP格式用的是两位年份（YY），解析时如果没指定世纪范围，系统可能会默认把17解析成2017，但如果有更早的时间（比如99），可能会被当成1999或2099，时间戳直接错了，排序肯定乱。
• 时区处理缺失：SMTP时间带时区标识（比如UTC），如果解析时忽略时区，把所有时间当成本地时间处理，不同时区的时间点会被错误对齐，计算间隔也会出问题。

正确的解决方法 #

核心思路：把SMTP字符串时间解析成原生的时间戳/日期时间对象，用数值而非字符串来排序和计算。

1. 在程序中解析处理

以Python为例，用datetime模块正确解析SMTP格式：

from datetime import datetime

smtp_time_str = 'Fri, 18 Aug 17 14:15:26 UTC'
# %a 是缩写星期几，%d 是日，%b 是缩写月份，%y 是两位年份，%Z 是时区
parsed_dt = datetime.strptime(smtp_time_str, '%a, %d %b %y %H:%M:%S %Z')
# 转成时间戳（秒级）用于排序/计算
timestamp = parsed_dt.timestamp()

注意：如果需要兼容跨世纪的两位年份，可以手动指定世纪范围（比如把00-49当成2000年后，50-99当成1900年后）。

2. 在数据库中直接转换排序

如果是数据库查询时排序，直接把字符串字段转成时间类型再排序，比如：

• PostgreSQL：

SELECT *
FROM your_table
ORDER BY TO_TIMESTAMP(smtp_message_time, 'Dy, DD Mon YY HH24:MI:SS TZ') ASC;

• MySQL：

SELECT *
FROM your_table
ORDER BY STR_TO_DATE(smtp_message_time, '%a, %d %b %y %H:%i:%s %Z') ASC;

容易遗漏的要点 #

• 格式一致性检查：确保所有smtp_message_time字段都严格符合SMTP格式，比如有没有少逗号、月份缩写大小写不一致（比如AUG而非Aug）、时区拼写错误（比如UTC写成Utc），这些都会导致解析失败，未解析的字符串混在结果里会打乱排序。
• 时区统一：如果数据里有不同时区的SMTP时间，解析后最好统一转成UTC时间再排序/计算，避免时区差异导致的时间点错位。
• 存储优化：如果频繁需要排序或计算时间间隔，建议在存储时直接存解析后的时间戳或日期时间类型，而不是原始字符串，这样每次查询不用重复解析，效率更高。

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