Re: Interval ordering

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(dot)klaver(at)gmail(dot)com