Dear Experts,
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.
Phil.
(You are welcome to CC: me in any replies.)