Re: Approximate join on timestamps

Phil Endecott wrote:
> 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 about:
t1 join t2
on ((t1.t - interval '30s', t1.t + interval '30s') overlaps (t2.t -
interval '30s', t2.t + interval '30s'))

No need for abs(interval) or repeating conditions that way. My first
attempt was using 'between' instead of 'overlaps', but I don't think
that'll work correctly.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //