postgresql - Postgres：如何按带时区的字符串日期排序？

Question

在我的 PostgreSQL 数据库中，我存储 JSONB，其中一个 JSON 节点是以下格式的日期字符串：

YYYY-MM-DD ，然后是 24 小时格式的小时、分钟和秒，然后是时区偏移量，如下所示：

2003-06-30 05:51:54+00:00 2003-06-30 14:25:45+10:00

但是，当我按 ASC 顺序对日期进行排序时，时间没有按时区偏移量正确排序，我认为这是因为 PostgreSQL 仍在查看日期，就好像它们是字符串一样。

SELECT header.id,
       header.data ->> 'date'    as "Date"
 FROM all_messages.sci_math_headers header
WHERE header.data ->> 'msgid' = 'xyz'
   OR header.data ->> 'ref' like '%xyz>%'
ORDER BY header.data ->> 'date' ASC
LIMIT 20;

因此，即使 2003-06-30 14:25:45+10:00 应该是第一个，它也会作为第二个结果出现。2003-06-30 05:51:54+00:00 2003-06-30 14:25:45+10:00

这是一个示例屏幕截图：

有没有办法直接在我的查询中将每个日期字符串转换为时间戳并正确排序？

标签： postgresqldate

Answer 1

将您的字符串转换为真正的时间戳：

SELECT header.id,
       header.data ->> 'date'    as "Date"
 FROM all_messages.sci_math_headers header
WHERE header.data ->> 'msgid' = 'xyz'
   OR header.data ->> 'ref' like '%xyz>%'
ORDER BY (header.data ->> 'date')::timestamptz ASC -- Changes here
LIMIT 20;

应该工作。