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