postgresql - Postgres : incorrect sorting if timestamp in smtp format

Question

我正在做一个项目，我们使用 SMTP 发送一些数据，如果时间戳已经以 smtp 格式存储，比如 'Dy, FMDD Mon YY HH24:MI:SS TZ，我会遇到一些奇怪的时间戳排序问题'(例如 'Fri, 18 Aug 17 14:15:26 UTC')。

一个孤立的案例，使用 smtp_message_time 排序:

psql=> select
             time,
             to_char(time, 'Dy, FMDD Mon YY HH24:MI:SS TZ') as smtp_time
         from
             messages
         order by
             smtp_time;

            time            |          smtp_time
----------------------------+-----------------------------
 2017-08-18 14:15:26.537+00 | Fri, 18 Aug 17 14:15:26 UTC
 2017-08-18 14:17:33.57+00  | Fri, 18 Aug 17 14:17:33 UTC
 2017-07-10 14:11:46.035+00 | Mon, 10 Jul 17 14:11:46 UTC
 2017-07-10 14:21:27.032+00 | Mon, 10 Jul 17 14:21:27 UTC
 2017-07-10 14:21:40.541+00 | Mon, 10 Jul 17 14:21:40 UTC
 2017-07-10 14:23:01.928+00 | Mon, 10 Jul 17 14:23:01 UTC
 2017-07-10 14:23:15.508+00 | Mon, 10 Jul 17 14:23:15 UTC
 2017-07-10 14:24:14.322+00 | Mon, 10 Jul 17 14:24:14 UTC
 2017-07-10 14:26:21.426+00 | Mon, 10 Jul 17 14:26:21 UTC
 2017-07-10 14:35:38.044+00 | Mon, 10 Jul 17 14:35:38 UTC
 ....
 2017-08-16 16:03:29.214+00 | Wed, 16 Aug 17 16:03:29 UTC
 2017-08-16 16:04:16.037+00 | Wed, 16 Aug 17 16:04:16 UTC
 2017-08-16 16:05:07.444+00 | Wed, 16 Aug 17 16:05:07 UTC
 2017-07-26 13:49:13.217+00 | Wed, 26 Jul 17 13:49:13 UTC
(96 rows)

我在一些最终结果中发现负区间后发现了这个问题。

1. 对这种行为有任何合乎逻辑的解释吗？
2. 是否有任何遗漏或我可以做些什么来正确解析格式？

如果可能的话，我宁愿将 smtp 格式作为我的第一选择而不是 Postgres 格式，因为它已经以这种方式存储并且需要完成一些代码返工。

旁注，我已经注意到我需要使用 interval(timestamp with time zone, timestamp with time zone) 来考虑时区，我还注意到我可以把一周的不同日子，结果保持不变。

Answer 1

PostgreSQL 在接受时间戳输入方面出奇地灵活。

SELECT 'Fri, 18 Aug 17 14:15:26 UTC'::timestamptz;

      timestamptz       
------------------------
 2017-08-18 16:15:26+02
(1 row)

所以你可以

ORDER BY smtp_time::timestamptz