| 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: | Whole Thread | Raw Message | 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 |