Re: Approximate join on timestamps

On Tue, 20 Mar 2007 23:30:46 +0000, "Phil Endecott" <spam_from_postgresql_general(at)chezphil(dot)org> wrote:
> 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)

How about using extract(epoch from t) to turn it into a numeric value?

select distinct on (t1.primary_key) *
from t1
join t2 on extract(epoch from t2.t) < extract(epoch from t1.t) + 30
and extract(epoch from t2.t) > extract(epoch from t1.t) - 30
order by t1.something,
abs(extract(epoch from t2.t) - extract(epoch from t1.t));

> What indexes could I add to make this moderately efficient?

If t is timestamp without time zone then you might be able to use an
index on it

create index t1_epoch_idx on t1 ((extract(epoch from t)))
create index t2_epoch_idx on t2 ((extract(epoch from t)))

> 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.

see order by. the +/- 30 in the above query can be used for tolerance
on the join.

> (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.)

see distinct on.

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+