Sort timestamps (including future) by absolute distance from "now"

Question

With a date field I can do this:

ORDER BY ABS(expiry - CURRENT_DATE)

With a timestamp field I get the following error:

function abs(interval) does not exist

Answer 1

Use now() or CURRENT_TIMESTAMP for the purpose.

The reason for the different outcome of your queries is this:

When you subtract two values of type date, the result is an integer and abs() is applicable.
When you subtract two values of type timestamp (or just one is a timestamp), the result is an interval, and abs() is not applicable. You could substitute with a CASE expression:

ORDER BY CASE WHEN expiry > now() THEN expiry - now() ELSE now() - expiry END

Or you can extract() the unix epoch from the resulting interval like @Craig already demonstrated. I quote: "for interval values, the total number of seconds in the interval". Then you can use abs() again:

ORDER BY abs(extract(epoch from (expiry - now())));

age() would just add a more human readable representation to the interval by summing up days into months and years for for bigger intervals. But that's beside the point: the value is only used for sorting.

As your column is of type timestamp, you should use CURRENT_TIMESTAMP (or now()) instead of CURRENT_DATE, or you will get inaccurate results (or even incorrect for "today").