Sort timestamps (including the future) at an absolute distance from the "now"

Question

With the date field, I can do this :

 ORDER BY ABS(expiry - CURRENT_DATE) 

The following error appears in the timestamp field:

Abs function (interval) does not exist

Answer 1

This works (and gives the correct sort):

 ABS(EXTRACT(DAY FROM expiry - CURRENT_TIMESTAMP)) 

Unfortunately, as Erwin Brandstätter pointed out, he reduces the sorting details to a full day.

Answer 2

Use now () or CURRENT_TIMESTAMP for this purpose.

The reason for the different result of your queries is the following:

When you subtract two values ​​of type date , the result will be integer and abs() .
When you subtract two values ​​of type timestamp (or only one of them is timestamp ), the result will be interval , and abs() not applicable. You can replace the CASE expression:

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

Or you can extract() unix epoch from the resulting interval , as shown in @Craig. 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() will simply add a more humanly readable representation to the interval by adding days to months and years for larger intervals. But this concerns the point: the value is used only for sorting.

Since your column has a type timestamp, you must use CURRENT_TIMESTAMP (or now() ) instead of CURRENT_DATE , or you will get inaccurate results (or even incorrect for today).

Answer 3

Compare with current_timestamp

 SELECT the_timestamp > current_timestamp; 

The age function, probably you want, comparing them:

 SELECT age(the_timestamp); 

eg:

 regress=# SELECT age(TIMESTAMP '2012-01-01 00:00:00'); age ---------------- 8 mons 17 days (1 row) 

If you want absolute distance, use:

 SELECT abs( extract(epoch from age(the_timestamp)) );