---------- Forwarded message ----------
From: Rhys Stewart <rhys.stewart@xxxxxxxxx>
Date: Mar 20, 2007 6:50 PM
Subject: Re: [GENERAL] Approximate join on timestamps
To: Phil Endecott <spam_from_postgresql_general@xxxxxxxxxxxx>
had a similar problem a while back. so i made and abs_time function:
CREATE OR REPLACE FUNCTION abs_time(interval)
RETURNS interval AS
$BODY$
BEGIN
if
$1 < '00:00:00'::interval
then
return ($1 * -1)::interval;
else
return $1;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION abs_time(interval) OWNER TO postgres;
hopes this gets you somewhere
On 3/20/07, Phil Endecott <spam_from_postgresql_general@xxxxxxxxxxxx> 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 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.)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
