Re: select and merge two tables with different granularity..

On 01/12/14 16:33, Lennart Jonsson wrote:
> On 12/01/2014 02:48 PM, The Natural Philosopher wrote:
>> This is a weird one.
>>
>> I have two tables. Table 1 contains 5 minute samples, table 2 contains
>> hourly samples.
>>
>> Let's say I want the last 240 samples of table one merged in time synch
>> with the last 20 samples of table 2, with each table 2 set repeated 12
>> times..
>>
>> how can this be done? It looks like some sort of join, but I can't for
>> the life of me see where to begin...???
>>
>> Can one join one row in table two to multiple rows in table one?
>>
>>
>
> You can extract date + hour info from both timestamps and join with that
>
>
> /Lennart
>
Hi Lennart.

Oddly enough I stumbled on te solution whilst trying to find it and failing

Select [stuff] from a, b
where

    a.timestamp > b.timestamp - interval 30 minute and

    a.timestamp < b.timestamp + interval 30 minute ;

got me the set I was after. A slow query, despite indices on timestamps, but only gets done every 5 minutes...

-- 
Everything you read in newspapers is absolutely true, except for the 
rare story of which you happen to have first-hand knowledge. – Erwin Knoll