Re: select and merge two tables with different granularity..
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Mon, 01 Dec 2014 19:40:51 +0000
Message-ID: <m5ig85$j1u$1_at_news.albasani.net>
>> On 01/12/14 16:33, Lennart Jonsson wrote:
>>>
>> 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...
>>
For some reason that gives me an empty result...
Date: Mon, 01 Dec 2014 19:40:51 +0000
Message-ID: <m5ig85$j1u$1_at_news.albasani.net>
On 01/12/14 18:11, Lennart Jonsson wrote:
> On 12/01/2014 06:06 PM, The Natural Philosopher wrote:
>> On 01/12/14 16:33, Lennart Jonsson wrote:
> [...]>>> /Lennart
>>> You can extract date + hour info from both timestamps and join with that
>>>
>>>
>>>
>> 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...
>>
> > Your solution is better than what I suggested. For readability you might > consider explicit join and a between predicate, just a thought (untested): > > select [stuff] > from a > join b > on a.ts between b.ts - interval 29 minute > and b.ts + interval 29 minute > > Since between is inclusive I subtracted 1 minute from the interval > > > /Lennart > >
For some reason that gives me an empty result...
-- Everything you read in newspapers is absolutely true, except for the rare story of which you happen to have first-hand knowledge. – Erwin KnollReceived on Mon Dec 01 2014 - 20:40:51 CET