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.