MySQL Forums
Forum List  »  General

Downsampliing data, but define interval start time?
Posted by: Martin Weil
Date: May 04, 2022 10:03AM

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

Options: ReplyQuote


Subject
Written By
Posted
Downsampliing data, but define interval start time?
May 04, 2022 10:03AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.