So when monitoring stuff which has a cyclic behavioural pattern derived from whatever(traffic on the roads etc), the ideal way to derived a baseline as a timeseries, would be to do something like:
select mean(field) where time = [same time, of same day as now()] for x amount of time in a continous query, so that I would get the average of eg. 10.15-10.20 for the past 10 fridays
This would allow me to compare now (eg friday 10.15) with the past 10 fridays at this the same time of day(baseline), to see of something was our of the ordinary.
But how to write that query?
I figured I could I could do something crude like this:
select field from measurement where
(((now()-1w10m)<time and time < (now()-1w)) or
((now()-2w10m)<time and time < (now()-2w)) or
((now()-3w10m)<time and time < (now()-3w)))
But apparently not:
“InfluxDB does not support using the
OR operator in the
WHERE clause to specify multiple time intervals.”
But I’d actually prefer something where it would just extrapolate over a given time - as in 'now()-10w)
I tried using time%(7*86400000000) to get a week - but apparently not all operators are allowed with time/now():
“All timestamp formats support basic arithmetic. Add (
+ ) or subtract (
- ) a time from a timestamp with a duration literal. Note that InfluxQL requires a whitespace between the
- and the duration literal.”
So I’m at a dead end, and still I’m thinking that I cannot be the first the have this requirement, so possibly theres a function or some easy way of doing this?
I did consider using holt-winters or similar, but I doubt that it would be able to predict a daily/weekly pattern?
Any straws I can grasp at is more than welcome?