在PostgreSQL中按"soonest"对带有"时间戳"的行进行排序

Question

我有一个事件表，每行包含一个可以为null的timestamptz列。有些价值观已经成为过去，而另一些则是未来。

我想要的是一个查询，它将返回按"；最快"；事件，过去的事件排序到列表的底部。

例如，如果该表具有以下值：

A  2021-06-25T00:00:00-05:00
B  2021-06-28T00:00:00-05:00
C  2021-06-30T00:00:00-05:00
D  2022-08-01T00:00:00-05:00
E  2021-07-01T00:00:00-05:00

和2021-06-29T00:00:00-05:00的NOW()时间，

我要按以下方式订购清单：

C  2021-06-30T00:00:00-05:00
E  2021-07-01T00:00:00-05:00
D  2022-08-01T00:00:00-05:00
B  2021-06-28T00:00:00-05:00
A  2021-06-25T00:00:00-05:00

Answer 1

我们的想法是首先按未来或过去对它们进行排序，然后按哪个日期最接近当前日期进行排序。

您可以将第一个排序表达式看作是将行分组。然后，第二个排序表达式将对每组中的行进行排序。(不要与使用GROUP BY的实际组混淆。(

您还可以将第二个排序表达式视为该日期和当前日期之间差值的绝对值。

我创建了一个临时表格来说明：

BEGIN;
CREATE TEMPORARY TABLE timestamps (
id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
value timestamptz
) 
ON COMMIT DROP;
INSERT INTO timestamps
(value)
VALUES
(NOW() + INTERVAL '1 week'),
(NOW() - INTERVAL '1 week'),
(NOW() + INTERVAL '2 weeks'),
(NOW() - INTERVAL '2 weeks'),
(NOw() + INTERVAL '3 weeks'),
(NOW() - INTERVAL '3 weeks');
SELECT
id, value
FROM timestamps
ORDER BY
NOW() < value DESC,
GREATEST(
value - NOW(),
NOW() - value
);
COMMIT;

Answer 2

使用此ORDER BY子句：

ORDER BY
/* primo, future values are always sorted first */
colname <= current_timestamp,
/* secundo, values closer to now are sorted first */
abs(EXTRACT(epoch FROM colname - current_timestamp))

这依赖于CCD_。

Answer 3

所以我已经完成了这项工作：

ORDER BY CASE
WHEN (event.event_date < NOW()) THEN (event.event_date + make_interval(50) - NOW())
ELSE event.event_date - NOW() END ASC

我不喜欢它。但无论如何，它近似于所期望的行为，因为未来的事件是按最快的顺序排列的，而过去的事件是在最后。make_interval(50)为过去的事件增加了50年。这是一个任意的数字，但它一直有效，直到出现更好的数字。