Average calculation procedure

Question

i'm implementing a mysql database for saving logged energy consumption data out of a smart home applications. The data then should be plotted within a javascript framework. Unfortunately the usage get's logged every 8 seconds and there's too much information to plot a year consumption graph.

The data gets saved in a simple table by it's time, device id and consumption at this specific time.

I'm hoping to be able to automatically aggregate the given data by minutes, hours and finally day average values.

After some research I came across some queries/procedures to calculate average values of specific intervals. Unfortunately this isn't much help to me as I have data over a period of three years and I don't want to create the given intervals by hand.

Ideally the procedure in mysql should be able to aggregate the given device values by it's time and calculate an average value and save it in a separate table.

Does anyone have a idea how I could implement it?

Answer 1

select avg(consumption) minute_average, date_format(log_date,'%m/%d/%y %H:%i') minute from data 
group by date_format(log_date,'%m/%d/%y %H:%i');

select avg(consumption) hour_average, date_format(log_date,'%m/%d/%y %H') hour from data 
group by date_format(log_date,'%m/%d/%y %H');

select avg(consumption) day_average, date_format(log_date,'%m/%d/%y') day from data 
group by date_format(log_date,'%m/%d/%y');

note: you could just as easily calculate any aggregate like sum or standard deviation as well.