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$@sbcglobal.net (view raw or flat)
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

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-2014 The PostgreSQL Global Development Group