Mysql – Query calculating AVG per minute GROUP BY timestamp

MySQL

I'm trying to query the average for each minute, so I can generate graph per 5, 15, 30 minutes.

Here is my table, date stored in MySQL DateTime data type field.

timestamp            vif_2_rx    
-------------------  ------------
2015-04-16 10:10:00  3.250400    
2015-04-16 10:14:40  593.508800  
2015-04-16 10:14:40  593.508800  
2015-04-16 10:24:40  153.693100   
2015-04-16 10:24:40  153.693100  
2015-04-16 10:29:55  54.976200   
2015-04-16 10:34:40  124.285100  
2015-04-16 10:34:40  124.285100  
2015-04-16 10:34:40  124.285100  
2015-04-16 10:39:55  49.292200   
2015-04-16 10:44:45  56.090700   
2015-04-16 10:44:45  56.090700   
2015-04-16 10:44:45  56.090700   
2015-04-16 10:49:55  20.641100   
2015-04-16 10:54:45  21.694600   
2015-04-16 10:54:45  21.694600

This is what I have tried so far.

SELECT TIMESTAMP, vif_2_rx FROM md_metrics_status WHERE vm_name = 'r-23-VM' 
AND TIMESTAMP > NOW() - INTERVAL 15 MINUTE 
GROUP BY MINUTE(TIMESTAMP);

Results:

TIMESTAMP            vif_2_rx    
-------------------  ------------
2015-04-16 10:04:40  94.752800   
2015-04-16 10:10:00  3.250400    
2015-04-16 10:14:40  593.508800  
2015-04-16 11:20:00  14.756200   
2015-04-16 10:24:40  153.693100  
2015-04-16 10:29:55  54.976200   
2015-04-16 13:30:00  76.920200   
2015-04-16 10:34:40  124.285100  
2015-04-16 10:39:55  49.292200   
2015-04-16 10:44:45  56.090700   
2015-04-16 10:49:55  20.641100   
2015-04-16 09:54:45  43.382700   
2015-04-16 11:59:55  78.070000

I'm guessing this would only pick a value from that minute without calculating the average of it, how can I go about calculating the average of each minute.

Any ideas on how I can go about doing that?

Thank you

Best Answer

You should use AVG function if you need to get the average
Using MINUTE function will return the minute part from the date. i.e. 10:05:03 and 11:05:33 will give the same result. (It is fine if the interval is less than 60 minutes, however, it is safer to use the following query:)

SELECT left(timestamp, 16) as mtimestamp, avg(vif_2_rx) FROM md_metrics_status WHERE vm_name = 'r-23-VM' AND TIMESTAMP > NOW() - INTERVAL 15 MINUTE GROUP BY mtimestamp

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure; but you could also use flat file stuctures that are meant for dealing with this type of data (eg, CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing. For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Best Answer

Related Solutions

Mysql – Storing a large number of small size entries in MySQL

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Related Question