James Nunnerley wrote: * Roger Baklund:
select date_format(ts,"%Y-%m-%d %H") period,sum(rcvd),sum(sent) from mytable group by period;
So the below query above will allow me to group by hour - which is quite useful - is there anyway of grouping by say 3 hour periods?
Not using the date_format() function, as far as I can tell. But you could convert your time to seconds, divide by 3*60*60=10800, and group by the result. Something like this:
select from_unixtime( floor(unix_timestamp(ts) / 10800) * 10800, "%Y-%m-%d %H+3h") period,sum(rcvd),sum(sent) from mytable group by period;
To get the actual first and last timestamp within each period, you could just add min(ts) and max(ts) to the field list.
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
