将整数范围排序为 PostgreSQL 日期范围

Question

因为PostgreSQL中的BC日期只在公元前4713年停止，而且我不想限制BC日期，所以我决定使用int作为日期。

这是我做select * from test order by daterange;后的表格：

id  daterange
26  [-6000501,-6000301)
27  [-6000401,-6000201)
22  [-4001202,-4000102)
23  [-4000702,-4000302)
19  [-3010102,-3000102)
21  [-3001202,-3000102)
14  [-3001011,-2000101)
15  [-1000506,6000701)
20  [3000102,3001201)
16  [12500709,13650230)
17  [14580102,16590507)

有表示按时间顺序排列的时代 int 范围。负数是公元前日期。模式是YYYYMMDD.

问题是id 27 [-6000401,-6000201)应该是第一位和id 26 [-6000501,-6000301)第二位，因为 BC 日期是翻转的：上限按时间顺序排在第一位。此外，一年中的几个月不会下降，而是上升。但是PostgreSQL试图保存负数，却不知道时间顺序的内容。所以它们应该这样排序：

1. -6000201，然后是 -6000401(公元前 600 年 2 月，然后是 4 月)
2. -6000301，然后 -6000501(公元前 600 年 3 月，然后是 5 月)

因此：

id 27 [-6000401,-6000201)  -- first
id 26 [-6000501,-6000301)  -- second

需要明确的是，我不想在范围内切换边界 - 这是不可能的。我想根据最大边界翻转排序顺序，就像上面一样。我不知道如何对这种范围进行排序。它应该根据它们的上限对它们进行排序，DESC我猜？我尝试了类似的东西

SELECT * FROM test  
ORDER BY daterange ASC 
, CASE WHEN (upper(daterange)<0) AND (lower(daterange)<0) THEN daterange END DESC;

但我得到同样的订单。我使用 PostgreSQL 10。

也许还有其他一些我根本没有想到的解决方法或方法。或者也许是一个无限 BC 日期的插件 - 即使我没有找到任何插件。

Answer 1

虽然坚持使用您的广告素材日期格式，但这应该达到正确的升序排序顺序：

SELECT *
, upper(daterange), upper(daterange)/10000, upper(daterange)%10000
, lower(daterange), lower(daterange)/10000, lower(daterange)%10000
FROM   test  
ORDER  BY CASE WHEN upper(daterange) >= 0 THEN daterange END NULLS FIRST 
-- BC before AD, fully sort AD
, upper(daterange)/10000       -- BC by start year
, upper(daterange)%10000 DESC  -- BC by start date
, lower(daterange)/10000       -- BC by end year
, lower(daterange)%10000 DESC; -- BC by end date

在这里小提琴

对于 BC 日期，CASE表达式默认为NULL，并且这些日期首先排序(尚未进一步区分)。关于NULLS FIRST：

• PostgreSQL按日期时间asc排序，null first？

接下来的 4 个表达式根据您的规则对 BC 日期进行排序。您可以向每个日期添加CASE WHEN upper(daterange) < 0，但由于 AD 日期已经完全排序，因此不会有什么不同。也许是为了性能，但不多。

Answer 2

PostgreSQLdate类型确实仅限于公元前4713年。但它可以达到公元5874897——将近 600 万年！

我希望您不需要存储所有600万年的日期。你可以做的是在你的日期上增加一百万(或两年)，你将适合PostgreSQL支持的范围。只是认为你已经改变了日期。

例如，您可以存储999400-02-01 AD(+1'000'000 年)，而不是600-02-01 BC。

您可以从int4range切换到daterange.您将免费获得适当的分类。

您需要记住的是，在显示日期之前减去您添加的年份。