Search Postgresql Archives

Re: Approximate join on timestamps

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux