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

Question

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!

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Nov 07 '14 at 19:40
Forgot to add what I had come up with so far. This does not work however, but this is the direction I was trying to go: CREATE TABLE TEMP1 SELECT local_time, Sum(in_bytes) AS sum_in_bytes, Sum(out_bytes) AS sum_out_bytes FROM (SELECT Convert_tz(time_stamp, '+00:00', '-07:00') AS local_time, id, max(in_bytes), out_bytes FROM video_hourly) AS TEMP2 GROUP BY Year(local_time), Month(local_time), Day(local_time), Hour(local_time) — user3822339, Nov 07 '14 at 20:06
Please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. — marc_s, Nov 07 '14 at 21:25

score 0 · Accepted Answer · edited May 23 '17 at 12:21

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.

OK. Great! I verified this against my data set and it checked out. I am trying to get the maximum value of the in_bytes column for each day, so I made one changed the SUM to the MAX function. That did the trick. Now I join back to the source table using the date, hour and max_total_in_bytes to get the out_bytes column to do my final sum of each max in_bytes and out_bytes column. Thank you! — user3822339, Nov 07 '14 at 20:51

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

1 Answers1

Linked