Fwd: Approximate join on timestamps

---------- Forwarded message ----------
From: Rhys Stewart <rhys(dot)stewart(at)gmail(dot)com>
Date: Mar 20, 2007 6:50 PM
Subject: Re: [GENERAL] Approximate join on timestamps
To: Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org>

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(at)chezphil(dot)org> 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>