From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Interval ordering |
Date: | 2012-01-29 22:48:22 |
Message-ID: | 201201291448.22968.adrian.klaver@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ondrej Ivanič | 2012-01-29 22:49:58 | Re: Interval ordering |
Previous Message | Raymond O'Donnell | 2012-01-29 22:40:56 | Re: FOSDEM booth volunteer |