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:

0
1
2
3
4
-1
-2
-3

Answer 1

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