Sort int range as PostgreSQL date range

Question

Because BC dates in PostgreSQL only stop at 4713 BC and I don't want to limit the BC dates in general, I decided to use int as dates.

Here is my table after doing 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)

There are int ranges that represent chronological eras. Negative numbers are the BC dates. The pattern is YYYYMMDD.

Problem is that id 27 [-6000401,-6000201) should be first and id 26 [-6000501,-6000301) second because BC dates are flipped: upper bounds are chronologically first. Also, months inside a year don't descent, they ascent. But PostgreSQL tries to save negative numbers, not having a clue about chronological content. So they should be ordered like so:

-6000201, then -6000401 (600BC February, then April)
-6000301, then -6000501 (600BC March, then May)

Therefore:

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

To be clear, I don't want to switch the bounds inside the ranges -that's impossible. I want to flip the sorting order according to the max bound, just like above. I don't know how to sort this kind of ranges. It should sort them according to their upper limit, DESC I guess? I tried something like

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

But I get the same order. I use PostgreSQL 10.

Maybe there is some other workaround or method that I didn't think at all. Or maybe a plugin for unlimited BC dates - even though I didn't find any.

@ErwinBrandstetter Thanks for the tips, edited my question – codebot Apr 12 '18 at 10:32 — codebot, Apr 12 '18 at 10:32
So do you have your answer? – Erwin Brandstetter May 17 '18 at 00:20 — Erwin Brandstetter, May 17 '18 at 00:20

Erwin Brandstetter · Answer 1 · 2018-04-12T10:36:52.280

While stuck with your creative date format, this should achieve the proper ascending sort order:

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

dbfiddle here

The CASE expression defaults to NULL for BC dates and those are sorted first (without differentiating further, yet). About NULLS FIRST:

PostgreSQL sort by datetime asc, null first?

The next 4 expressions sort BC dates according to your rules. You might add CASE WHEN upper(daterange) < 0 to each, but since AD dates are fully sorted already, it wouldn't make a difference. Maybe for performance, but not much.

score 0 · Answer 2 · answered Apr 11 '18 at 23:23

PostgreSQL date type is indeed limited to 4713 BC. But it can reach 5874897 AD - almost 6 million years!

I hope that you don't need to store dates in all 6 million years range. What you can do is to add a million (or two) years to your dates and you will fit in the range that PostgreSQL supports. Just think that you have shifted the dates.

For example, instead of 600-02-01 BC you can store 999400-02-01 AD (+1'000'000 years).

And you can switch from int4range to daterange. You will get proper sorting for free.

All you need to remember is to subtract the years that you have added before displaying the date.

Sort int range as PostgreSQL date range

2 Answers2