Fwd: Approximate join on timestamps

---------- Forwarded message ----------
From: Rhys Stewart <rhys.stewart@gmail.com>
Date: Mar 20, 2007 6:50 PM
Subject: Re: [GENERAL] Approximate join on timestamps
To: Phil Endecott <spam_from_postgresql_general@chezphil.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@chezphil.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@postgresql.org so that your
>        message can get through to the mailing list cleanly
>