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?
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 |