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

Question

I have a date field that displays as the number of days from today. Thus, 2055-01-01 and 1950-01-01 will be displayed as positive and negative numbers, respectively. Now I would like them to be ordered so that non-negative numbers come first, in ascending order, then negative numbers come in descending order. For instance:

 0 1 2 3 4 -1 -2 -3 

Answer 1

The following actions will also work:

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

However, this form will not use the index to create rows in the desired order. If your query benefits from this (selects the majority of the rows from the table or uses the limit), you need to use a join:

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

Answer 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 substitute "i" for your diff calculation and @t for your table, this should work

Answer 3

I cracked it using this order:

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

Since dates are for several years, it is safe to promote negative dates in the future. Then their distance from the current date is canceled.

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