views:

67

answers:

2

I'm trying to work out an SQL statement for MySQL. I have a series of stats for a range of servers that are reported on a half hourly basis. I have a stats table with columns similar to this:

server varchar(64), 
time datetime,
bytesIn int,
bytesOut int

Now, I want to generate a series of reports that collate all of the bytesIn and bytesOut on a either 1 hour or 1 day period across all servers. ie. I know I need SUM(bytesIn) as bytesIn, SUM(bytesOut) as bytesOut, but don't know how to 'clump' all of the times into hourly/daily buckets and also return that 'clumped' time. That is, the returned data should look something like this:

Time,bytesIn,bytesOut
12:00,1024,2048
13:00,4096,8192
etc

I know this should be simple, but I'm lost. Can anyone help?

A: 

would something like this work?

SELECT substr( time, 12, 2) AS time,  sum( bytesIn ) AS bytesIn, sum( bytesOut ) AS bytesOut 
GROUP BY substr( time, 12, 2)

it would rely on your timestamp field always having a standard yyyy-mm-dd-hh.nn.ss format, but I'm sure you could work around that with to_date or something - don't know how you've stored your timestamps...

hgh
times are stored as 'datetime'. ie dates. I'll give it a go
cmroanirgo
+2  A: 

Perhaps you can use the DATE_FORMAT() function, and grouping. Here's an example, hopefully you can adapt to your precise needs.

SELECT
    DATE_FORMAT( time, "%H:%i" ),
    SUM( bytesIn ),
    SUM( butesOut )
FROM
    stats
WHERE
    time BETWEEN <start> AND <end>
GROUP BY
    DATE_FORMAT( time, "%H:%i" )

If your time window covers more than one day, and you use the example format, data from different days will be aggregated into the 'hour-of-day' buckets. If the raw data don't fall exactly on the hour, you can smooth that out by using "%H:00".

martin clayton
Thanks! That is the perfect answer, and yes, I had to replace %i with 00 in order to smooth out the data
cmroanirgo