postgresqldatetimesql-order-bypostgresql-8.2

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

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

Solution

  • This works (and gives the correct sorting):

    ABS(EXTRACT(DAY FROM expiry - CURRENT_TIMESTAMP))

    Unfortunately, as Erwin Brandstetter pointed out, it reduces the granularity of the sorting to a full day.