Thread: Interval ordering

Interval ordering

From
"Adam Rich"
Date:

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?

 

 

 

Re: Interval ordering

From
Adrian Klaver
Date:
On Sunday, January 29, 2012 2:19:38 pm Adam Rich wrote:
> 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
>

>
>
> Is this ugly query really necessary for postgres?


How about something like:

test(5432)aklaver=>\d timestamp_test
           Table "public.timestamp_test"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 id      | integer                     | not null
 txt_fld | text                        |
 ts_fld  | timestamp with time zone    |
 ts_fld2 | timestamp(0) with time zone |


test(5432)aklaver=>SELECT ts_fld2,now()-ts_fld2 from timestamp_test order by
now()-ts_fld2 limit 5;
        ts_fld2         |         ?column?
------------------------+--------------------------
 2011-03-25 14:16:27-07 | 310 days 01:16:04.881631
 2011-03-25 14:15:13-07 | 310 days 01:17:18.881631
 2010-05-20 13:13:54-07 | 619 days 02:18:37.881631
 2010-05-20 12:13:28-07 | 619 days 03:19:03.881631
 2010-05-20 10:13:43-07 | 619 days 05:18:48.881631


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Interval ordering

From
"Adam Rich"
Date:
> How about something like:
> test(5432)aklaver=>SELECT ts_fld2,now()-ts_fld2 from timestamp_test order
by
>  now()-ts_fld2 limit 5;

Thanks Adrian,
Let me explain the problem better.  Say my table has 24 entries, one for
each
hour, midnight through 11 pm.  If the user enters "6:30 PM", I want to give
them
the closest times in proximity - both before AND after - to their input...
so in this
case, I'd return 5:00 PM, 6:00 PM, 7:00 PM, 8:00 PM, etc.

I believe your solution only provides the closest times BEFORE and not
AFTER.




Re: Interval ordering

From
Adrian Klaver
Date:
On Sunday, January 29, 2012 2:39:12 pm Adam Rich wrote:
> > How about something like:
> > test(5432)aklaver=>SELECT ts_fld2,now()-ts_fld2 from timestamp_test order
>
> by
>
> >  now()-ts_fld2 limit 5;
>
> Thanks Adrian,
> Let me explain the problem better.  Say my table has 24 entries, one for
> each
> hour, midnight through 11 pm.  If the user enters "6:30 PM", I want to give
> them
> the closest times in proximity - both before AND after - to their input...
> so in this
> case, I'd return 5:00 PM, 6:00 PM, 7:00 PM, 8:00 PM, etc.
>
> I believe your solution only provides the closest times BEFORE and not
> AFTER.

test(5432)aklaver=>SELECT ts_fld2,abs(extract(epoch from '2011-03-25
14:15:25-07'::timestamptz)-extract(epoch from ts_fld2)) from timestamp_test order
by abs(extract(epoch from '2011-03-25 14:15:25-07'::timestamptz)-extract(epoch
from ts_fld2)) limit 5;
        ts_fld2         |   abs
------------------------+----------
 2011-03-25 14:15:13-07 |       12
 2011-03-25 14:16:27-07 |       62
 2010-05-20 13:13:54-07 | 26701291
 2010-05-20 12:13:28-07 | 26704917
 2010-05-20 10:13:43-07 | 26712102

Pardon for the scrambled format.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Interval ordering

From
Ondrej Ivanič
Date:
Hi,

On 30 January 2012 09:19, Adam Rich <adam.r@sbcglobal.net> wrote:
> 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

try this:
select mytime from mytable order by abs(extract(epoch from
(usertime-mytime))) asc limit 5;

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)

Re: Interval ordering

From
"Adam Rich"
Date:
>> try this:
>> select mytime from mytable order by abs(extract(epoch from
>> (usertime-mytime))) asc limit 5;

>> SELECT ts_fld2,abs(extract(epoch from '2011-03-25
>> 14:15:25-07'::timestamptz)-extract(epoch from ts_fld2)) from timestamp_test order
>> by abs(extract(epoch from '2011-03-25 14:15:25-07'::timestamptz)-extract(epoch
>> from ts_fld2)) limit 5;

Thanks to Ondrej and Adrian, who both provided working solutions using extract/epoch.