Hi there,
my database contains measurement data for every second. But - since there is not always a measurement going - there are gaps inbetween.
Now I want to do a downsampling of said data (i.e. "give me one week with a 60-minute-average).
What I tried so far:
(the idea is from here:
http://www.agcross.com/2014/11/resample-mysql-datetime-interval/)
SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(datetime) div (3600))*(3600)+(3600)) as FinalStamps,
round(avg(col1),1) as col1,
round(avg(col2),1) as col2,
round(avg(col3),1) as col3
FROM `myTable`
GROUP BY 1
This works well and reasonably fast.
But I need this modified, so that I can define WHEN an interval should start. I need to illustrate that:
If I want a 2-hour-average for a time period from 00:30 to 11:00, I would include an "WHERE datetime between 00:30:00 and 11:00:00". But this only produces 6 intervals,the first ranging from 00:00 to 01:59 and the last from 10:00 to 11:59.
But I need my intervals to start precisely at my given start time, so the first one need to range from 00:30 to 02:30.
Does anyone have any ideas for me?
Thanks very much
Martin