Approximate join on timestamps

From: "Phil Endecott" <spam_from_postgresql_general(at)chezphil(dot)org>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Approximate join on timestamps
Date: 2007-03-20 23:30:46
Message-ID: 1174433446063@dmwebmail.belize.chezphil.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John DeSoi 2007-03-20 23:49:21 Re: "sniffing" postgres queries
Previous Message Dhaval Shah 2007-03-20 23:01:26 Re: Unable to start postgres in recovery mode.