postgresql - Postgres : How to sort by string date with timezone?

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

这是一个示例屏幕截图:



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

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;

应该工作。