Thread: Interval ordering
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?
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
> 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.
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
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)
>> 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.