Interval ordering

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Interval ordering
Date: 2012-01-29 22:19:38
Message-ID: 006101ccded4$179e8700$46db9500$@sbcglobal.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have a table with a list of times. When the user provides my application
with a

desired time, I want to show them the 5 times from the table that are
closest to their

input. I expected to do this using abs() like such:

select mytime from mytable order by abs(usertime-mytime) asc limit 5;

However, the difference between times is an interval, and there appears to
be no

absolute value operator for those. My next thought was to convert the
interval

into integer like such:

select mytime from mytable order by abs((usertime-mytime) / interval '1
minute') asc limit 5;

However. there is no operator for interval division either. The best
solution I've come up

with is to use a case statement.

select mytime from mytable

order by case when (usertime-mytime) < interval '0'

then (mytime-usertime)

else (usertime-mytime) end asc limit 5;

Is this ugly query really necessary for postgres?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message gvim 2012-01-29 22:27:07 MS Access easier with PostgreSQL or MySQL?
Previous Message Oleg Bartunov 2012-01-29 22:06:34 Re: Simplifying the tsvector format for simple glossaries