How to sort by distance from now() (future first) in PostgreSQL

Question

I've got a date field which is displayed as the number of days from today. So 2055-01-01 and 1950-01-01 would be displayed as positive and negative numbers respectively. Now I'd like these to be ordered so that the non-negative numbers come first, in ascending order, then negative numbers come in descending order. For example:

score 4 · Accepted Answer · answered Sep 13 '12 at 08:15

4

The following would also work:

 ORDER BY expiry < CURRENT_DATE, abs(expiry - CURRENT_DATE)

However this form won't use an index to produce the rows in the desired order. If your query would benefit from that (selects most of the rows from the table or uses a limit), then you'll need to use a union:

SELECT ... WHERE ... AND expiry >= CURRENT_DATE ORDER BY expiry
    UNION ALL
SELECT ... WHERE ... AND expiry < CURRENT_DATE ORDER BY expiry DESC

answered Sep 13 '12 at 08:15

Ants Aasma

53,288
15
90
97

+1 Of course! I ended up using the first solution, because the query is rather long, and the speed is acceptable for now. – l0b0 Sep 13 '12 at 09:57
This doesn't work on PostgreSQL 12: `function abs(interval) does not exist` – Kiran Jonnalagadda Jul 13 '20 at 00:42

score 2 · Answer 2 · answered Sep 13 '12 at 08:26

2

select * from @t
order by 
case when i >= 0 then 0 else 1 end,
case when i >= 0 then i else abs(i) end

if you substitue 'i' for your diff calculation and @t for your table this should work

answered Sep 13 '12 at 08:26

Rich

331
2
7

score 1 · Answer 3 · answered Sep 13 '12 at 07:55

I've hacked around it with this order by clause:

ORDER BY CASE expiry >= CURRENT_DATE
         WHEN true THEN expiry
         ELSE CURRENT_DATE + (CURRENT_DATE - expiry) + interval '1000 years'
         END

Since dates are within a few years of today, it's safe to push the negative dates way into the future. Then their distance from the current date is reversed.

It works, but a more elegant solution would be nice.

How to sort by distance from now() (future first) in PostgreSQL

3 Answers3

Linked