Skip site navigation (1) Skip section navigation (2)

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$ (view raw or whole thread)
Lists: pgsql-general

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

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?





pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group