Use different table according to time range

I have a time series data and the interval between 10s(Let’s call this table as log_raw). For a capacity reason, I delete every data that is older than 7 days. So when the user requires data older than 7 days, I cannot provide.

But, I have summarized table which has an interval an hour(Let’s call this table log_by_hour). This table has data older than 7 days, so I came up with an idea that when the user requires older than 7-day data, provide that data with log_by_hour.

So, Is there any way I can switch the table according to the time range user requested?

Is there any trick for this problem?

Thanks a lot!

I would try something like two SELECTs and add the results together - one of
them will return data, the other will not; the sum should give you whatever
data you have.

So, for example:

select r+v from (select value as r from log_raw),(select value as h from
log_by_hour) where…

Obviously the exact syntax will depend on which back-end data store you’re
using and the details of its query language.

Regards,

Antony.

1 Like

That’s an idea! Thx. My backend is MySql and I will post the query when I succeed.

Okay, if you’re using MySQL then try:

select raw.value+hour.value from (select value from log_raw) as raw,(select
value from log_by_hour) as hour where …

Antony.

I used UNION ALL and substr to change the table according to the time range

SELECT a.time, a.diffValue diff, "RPUSH" metric
FROM (
         SELECT l.instance_id, l.created     AS "time",
                l.value as diff,
                if( BINARY @lastInstanceId = BINARY l.instance_id, l.value - @lastValue, 0 ) as diffValue,
                @lastInstanceId := l.instance_id lcmdId,
                @lastValue := l.value lvalue
         FROM log_raw l, (select @lastInstanceId:='',@lastValue:=0 ) lv
         WHERE l.cmd_id = 7
           AND l.instance_id =$instance_id
           AND $__timeFilter(l.created)
           AND substr('$__interval',-1)='s'
         UNION ALL
         SELECT l.instance_id, l.time     AS "time",
                l.avg as diff,
                if( BINARY @lastInstanceId = BINARY l.instance_id, l.avg - @lastValue, 0 ) as diffValue,
                @lastInstanceId := l.instance_id lcmdId,
                @lastValue := l.avg lvalue
         FROM log_by_minute l, (select @lastInstanceId:='',@lastValue:=0 ) lv
         WHERE l.cmd_id = 7
           AND l.instance_id =$instance_id
           AND $__timeFilter(l.time)
           AND substr('$__interval',-1)='m'
         UNION ALL
         SELECT l.instance_id, l.time     AS "time",
                l.avg as diff,
                if( BINARY @lastInstanceId = BINARY l.instance_id, l.avg - @lastValue, 0 ) as diffValue,
                @lastInstanceId := l.instance_id lcmdId,
                @lastValue := l.avg lvalue
         FROM log_by_hour l, (select @lastInstanceId:='',@lastValue:=0 ) lv
         WHERE l.cmd_id = 7
           AND l.instance_id =$instance_id
           AND $__timeFilter(l.time)
           AND substr('$__interval',-1) in ('h','d')
     ) a
ORDER BY a.time