MySQL: Using SQL having trouble figuring out the busiest hour for each day that has the maximum "in_bytes"? -

MySQL: Using SQL having trouble figuring out the busiest hour for each day that has the maximum "in_bytes"? -

goal: write right sql solve problems below.

part 1:

having problem figuring out sql statement on how timestamp includes date , hr have maximum "in_bytes" each day. see "video_hourly" table ddl code below. if there 2 maximum values have same value in given day pick first one. info beingness graphed in highcharts there can 1 info point each given day. can fill table sample data.

part 2:

another part of problem 1 time have of unique maximum "in_bytes" each day need sum "in_bytes" , "out_bytes" 1 record.

to convert utc time database local time using in queries:

select time_stamp,convert_tz(time_stamp, '+00:00', '-07:00' ) localtime

here ddl sql table:

create table video_hourly ( id bigint(20) not null auto_increment, time_stamp datetime not null, in_bytes bigint(20) unsigned not null default 0, out_bytes bigint(20) unsigned not null default 0, opt_pct decimal(11, 2) not null default 0.00, primary key (id) ) engine = innodb;

any help or advice on appreciated. give thanks you!

see list of datetime functions can use. specifically, can utilize hour() hr value.

you can utilize date() date part of datetime column. 1 time have those, can grouping them together. seek , break downwards you.

this homecoming date, hour, , in_bytes hour, grouping day , hour.

select date(time_stamp) date, hour(time_stamp) hour, sum(in_bytes) totalinbytes video_hourly grouping date, hr order date, hour, totalinbytes desc;

this max totalinbytes @ top of each grouping because orders in descending order.

also, please see this question how max value in group, in case want max inbytes each date.

then, can alter query this:

select concat(v.date, ' ', v.hour) dateandhour, v.totalinbytes from(select time_stamp fulldate, date(time_stamp) date, hour(time_stamp) hour, sum(in_bytes) totalinbytes video_hourly grouping date, hr order date, hour, totalinbytes desc ) v where( select count(*) from(select date(time_stamp) date, hour(time_stamp) hour, sum(in_bytes) totalinbytes video_hourly grouping date, hr order date, hour, totalinbytes desc ) vh vh.date = v.date , vh.totalinbytes >= v.totalinbytes ) <= 1;

i can't seek without sample data, here sql fiddle link, if want seek out. used create sure not produce errors.

mysql hour