Re: Approximate join on timestamps

[Brent Wood <b.wood@xxxxxxxxxx>]

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

Hi Phil,

This is how we dealt with a similar situation. It may be suitable for 
you....


We have about 200,000,000 records timestamped to facilitate this using a 
more or less data warehousing approach.

We generate timestamps at one minute intervals, then assign the 
appropriate values (readings) to each timestamp, by using the last 
recorded reading before that time (separate record for each instrument) 
within an appropriate interval (so missing values are not populated with 
historic values). Note that this will discard all records for each 
reading except for the last one per interval.

We also add a column ("timer") which has values of 1, 2, 5, 10, 20, 30, 
60, 720, 1440 depending on the hour & minutes of the timestamp.

so a "where timer >=60" returns hourly readings, "where timer =720" 
gives midday readings, "where timer >=10" gives every 10 minute reading, 
etc

This then gets a clustered index on timestamp (and is partitioned on 
year) and a 24 way self-relation (ie: 24 instrument readings joined by 
timestamp returns 3 months of 10 minute values in 20 odd seconds on a 
fastish desktop box.

HTH,

 Brent Wood