Re: Interval ordering

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