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

On 12/01/2014 06:06 PM, The Natural Philosopher wrote:
> On 01/12/14 16:33, Lennart Jonsson wrote:
[...]
>> 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...
>

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 Received on Mon Dec 01 2014 - 19:11:08 CET