MySQL: Using SQL having trouble figuring out the b

Goal: Write the correct SQL to solve the problems below.

Part 1:

Having trouble figuring out the SQL statement on how to get the timestamp that includes the date and the hour where you have the maximum "in_bytes" for each day. See "video_hourly" table DDL code below. If there are two maximum values that have the same value in a given day just pick the first one. This data is being graphed in highcharts so there can only be one data point for each given day. You can fill the table with some sample data.

Part 2:

Another part of this problem is once you have all of the unique maximum "in_bytes" for each day then you need to sum the "in_bytes" and "out_bytes" to get one record.

To convert the UTC time from the database to local time we using this in the queries:

SELECT time_stamp,CONVERT_TZ(time_stamp, '+00:00', '-07:00' ) as localtime

Here is the DDL SQL for the 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 this would greatly be appreciated. Thank you!

See this list of datetime functions that you can use. Specifically, you can use HOUR() to get the hour value.

You can also use DATE() to get the date part of a datetime column. Once you have those, you can group them together. I will try and break it down for you.

This will return the date, hour, and the in_bytes for that hour, by grouping by day and hour.

SELECT DATE(time_stamp) AS date, HOUR(time_stamp) AS hour, SUM(in_bytes) AS totalInBytes
FROM video_hourly
GROUP BY date, hour
ORDER BY date, hour, totalInBytes DESC;

This will also but the max totalInBytes at the top of each group because it orders by that in descending order.

Also, please see this question for how to get the max value in a group, which in this case is you want to get the max inBytes for each date.

Then, you can change your query to this:

SELECT CONCAT(v.date, ' ', v.hour) AS dateAndHour, v.totalInBytes
FROM(SELECT time_stamp AS fullDate, DATE(time_stamp) AS date, HOUR(time_stamp) AS hour, SUM(in_bytes) AS totalInBytes
    FROM video_hourly
    GROUP BY date, hour
    ORDER BY date, hour, totalInBytes DESC
) v
WHERE(
    SELECT COUNT(*)
    FROM(SELECT DATE(time_stamp) AS date, HOUR(time_stamp) AS hour, SUM(in_bytes) AS totalInBytes
        FROM video_hourly
        GROUP BY date, hour
        ORDER BY date, hour, totalInBytes DESC
    ) vh
    WHERE vh.date = v.date AND vh.totalInBytes >= v.totalInBytes
) <= 1;

I can't try it without any sample data, but here is an SQL Fiddle link, if you want to try it out. I used this to make sure it would not produce any errors.