Re: Approximate join on timestamps

"Phil Endecott" <spam_from_postgresql_general(at)chezphil(dot)org> writes:

> I have two tables containing chronological data, and I want to join them using
> the timestamps. The challenge is that the timestamps only match approximately.
>
> My first attempt was something like
>
> t1 join t2 on (abs(t1.t-t2.t)<'1 min'::interval)
>
> Of course there is no "abs" for intervals, and I couldn't think of anything
> better than this
>
> t1 join t2 on (t1.t-t2.t<'1 min'::interval and t2.t-t1.t<'1 min'::interval)
>
> What indexes could I add to make this moderately efficient?
>
> But that query isn't really good enough. There is no single "epsillon" value
> that works for this data set. I really want to find the closest match.
>
> I feel that it ought to be possible to step through the two tables in
> timestamp order matching up elements. Is there any way to express this is SQL?
>
> (One detail is that the left table has fewer rows than the right table, and I
> want one output row for each row in the left table.)
>
> Many thanks for any suggestions.

Untested, but what about something like a function that does (pseudocode below):

select (min(t1.t) > ref_time) as above_t1
select (max(t1.t) < ref_time) as below_t1
if ((above_t1 - below_t1) =< '0 seconds'::interval then
return above_t1
else
return below_t1

to find out the nearest time with regards to t1 when compared to a reference
time that should be the time you're looking for.

Do the same for t2...

I haven't checked the docs if there's something that already makes your life
easier :-)

--
Jorge Godoy <jgodoy(at)gmail(dot)com>