8

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

Community
  • 1
  • 1
l0b0
  • 55,365
  • 30
  • 138
  • 223
  • 2
    PostgreSQL 8.2 has reached end-of-life in Dec. 2011. Consider [upgrading to a current version](http://www.postgresql.org/support/versioning/). – Erwin Brandstetter Sep 17 '12 at 14:35
  • Thanks, I'll look into it (it's not my decision). – l0b0 Sep 17 '12 at 14:40
  • @Charles: I intended to start the transition from [order-by] to [sql-order-by] according to the discussion [here](http://meta.stackexchange.com/q/141455/169168). Do you disagree? – Erwin Brandstetter Sep 17 '12 at 18:32
  • @ErwinBrandstetter, oh, no, I just hadn't noticed that topic. Yeah, sounds like a sane plan. Looks like 1500 questions still... good luck. I'll throw in some help if I get some time. – Charles Sep 17 '12 at 18:40

3 Answers3

15

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").

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I don't get it. Using `CURRENT_TIMESTAMP` instead of `CURRENT_DATE` makes no difference. – l0b0 Sep 17 '12 at 14:03
1

Compare with current_timestamp

SELECT the_timestamp > current_timestamp;

The age function is probably what you want when 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 an absolute distance, use:

SELECT abs( extract(epoch from age(the_timestamp)) );
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
1

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.

l0b0
  • 55,365
  • 30
  • 138
  • 223
  • This should basically work, but simplifies everything to a matter of days, thereby lumping all timestamps together indiscriminately in between 24h-ranges of data. If you want to go with [`EXTRACT`](http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT) use `epoch` or `milliseconds` instead of `DAY`. – Erwin Brandstetter Sep 17 '12 at 14:27
  • @ErwinBrandstetter: `MILLISECONDS` seems to be modulo the day, so that results in "random" sorting. But `EXTRACT(EPOCH FROM expiry) - EXTRACT(EPOCH FROM NOW())` works beautifully. Could you make this your answer? – l0b0 Sep 17 '12 at 14:38
  • Right, `milliseconds` would have to be *in addition* to `day`. `epoch` is simpler. I added to my answer. – Erwin Brandstetter Nov 22 '14 at 08:23