Select MySql rows for who is logged in for date range, return with hour count per day

Question

I have a headache of a SQL query to write, hopefully someone in internet world can help me!

I have a mysql table with values of when people log in and log out and I need to report data for a date range that gives the number of people logged in for each hour of the day.

Example desired output:

2013-1-10 00:00 5
2013-1-10 01:00 13
... for every hour of 2013-1-10 ending at 23:00...
2013-1-10 23:00 23
...and then the same for every date in the range...
2013-1-11 00:00 4
2013-1-11 01:00 6

I can write the query to group everything by date and get the count for each hour based on time login, the problem is that if people are logged in or longer than an hour their count won't show up in the second hour because I'm grouping by DATE_FORMAT(time_login, '%H').

Example query is here:

SELECT mac, 
DATE_FORMAT(time_login, '%H') AS hour_logged_in,
DATE_FORMAT(time_logout, '%H') AS hour_logged_out,
DATE_FORMAT(time_login, '%Y-%m-%d') as date,
count(*) as dealcounts 
FROM sessions 
WHERE (
    time_logout IS NOT NULL 
     ) 
AND (
    DATE_FORMAT(time_login, '%Y-%m-%d') BETWEEN 
    DATE_FORMAT('2013-1-1', '%Y-%m-%d') AND DATE_FORMAT('2013-1-18', '%Y-%m-%d')) 
GROUP BY hour_logged_in

Does anyone know how I can accomplish getting a count of rows that are logged in for every hour of the day?

I have a sqlfiddle set up here: http://www.sqlfiddle.com/#!2/e13a0/7 with the above query

Extra credit: Null values for hours with no one logged in :-)

Happy to paypal some beers & coffees to anyone who can help!!! Thanks!

Answer 1

I think the mysql code further below is close to what you are looking for?

Notes

1. It is not elegant unless it's elegant in the way that a major hack can be thought elegant. First look will probably cause moderate to severe intestinal/cerebral cramping over the sheer ugliness of it.

2. It would need additions of indices and such for real usage, is my guess.

3. Note that I changed the name of the sessions table to sessions_example. I didn't want to nuke your sessions table if you execute this.

4. Finally, look for the results in a new table called hours_expanded.

5. It needs more testing. I'm worn out right now, so I'm submitting the potential solution. I think the general approach is right and the code seems right, but verify!

6. I handle actively logged in records by extending the logged in count by hour up to the present hour (NOW()). If it's possible for someone to logout without that logout always making it to the sessions table, you might need to do something different. I.e. come up with a max cutoff time for records marked as active.

General Approach

Basically, when I read the question, I knew that my preferred approach would be to "expand" all the hours involved into actual table rows. I've found this to be preferable in situations like this for testing and understanding, even though it's not necessarily a great performer. As I was figuring out how to do this, I found a blog post http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html, (see reference to Roland Bouman and Kai Voigt) that really, really helped me get the row expansion I wanted. I'm used to doing this kind of thing in MSSQL with a CTE--so I needed that help to find a way to produce an arbitrary number of rows in MySQL.

Here goes:

/* Following test data from provided sql fiddle */

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*changed name of sessions table to sessions_example, so as not to interfere with any prior sessions table*/
DROP TABLE IF EXISTS sessions_example;
CREATE TABLE `sessions_example` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `network_id` int(11) NOT NULL,
  `mac` varchar(17) NOT NULL,
  `time_login` datetime NOT NULL,
  `time_logout` datetime DEFAULT NULL,
  `data_in` bigint(20) DEFAULT NULL,
  `data_out` bigint(20) DEFAULT NULL,
  `sessionid` varchar(20) NOT NULL,
  `user_ip` varchar(20) DEFAULT NULL,
  `external_ip` varchar(20) DEFAULT NULL,
  `opfield1` varchar(255) DEFAULT NULL,
  `opfield2` varchar(255) DEFAULT NULL,
  `opfield3` varchar(255) DEFAULT NULL,
  `txtfield4` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `sessionid` (`sessionid`),
  KEY `time_login` (`time_login`),
  KEY `time_logout` (`time_logout`),
  KEY `ap_id` (`network_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1009253406 ;

INSERT INTO `sessions_example` (`id`, `user_id`, `network_id`, `mac`, `time_login`, `time_logout`, `data_in`, `data_out`, `sessionid`, `user_ip`, `external_ip`, `opfield1`, `opfield2`, `opfield3`, `txtfield4`) VALUES
(1009250911, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 15:45:05', '2013-01-07 16:55:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250912, 0, 254, '00-88-65-D9-B1-8E', '2013-01-07 15:50:04', '2013-01-07 19:11:58', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250914, 0, 254, 'D0-23-DB-17-DC-FA', '2013-01-07 16:20:04', '2013-01-07 17:35:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250915, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 16:25:01', '2013-01-07 16:50:04', NULL, NULL, 'active', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250917, 0, 254, 'E0-B9-BA-F0-45-83', '2013-01-07 16:45:05', '2013-01-07 19:11:58', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250919, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 16:55:05', '2013-01-07 17:12:30', NULL, NULL, 'active', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250920, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 17:12:30', '2013-01-07 17:15:06', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250921, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 17:15:06', '2013-01-07 19:11:59', NULL, NULL, 'active', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250926, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 19:11:59', '2013-01-07 20:30:04', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250927, 0, 254, '00-1B-9E-75-3E-DA', '2013-01-07 19:11:59', '2013-01-07 20:45:04', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250929, 0, 254, '00-88-65-D9-B1-8E', '2013-01-07 20:35:04', '2013-01-07 22:15:04', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250930, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 20:50:04', '2013-01-07 22:25:04', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250931, 0, 254, '00-26-C7-CD-10-9C', '2013-01-07 21:10:04', NULL, NULL, NULL, 'active', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250933, 0, 254, '00-1B-9E-75-3E-DA', '2013-01-07 21:30:04', '2013-01-08 00:20:04', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250934, 0, 254, '00-88-65-D9-B1-8E', '2013-01-07 23:30:04', '2013-01-08 00:05:04', NULL, NULL, 'active', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250935, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-07 23:50:04', '2013-01-08 00:50:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250936, 0, 254, '00-88-65-D9-B1-8E', '2013-01-08 00:05:04', '2013-01-08 02:20:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250940, 0, 254, '00-26-C7-CD-10-9C', '2013-01-08 01:10:05', '2013-01-08 02:10:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250941, 0, 254, '5C-0A-5B-5E-5D-C3', '2013-01-08 01:25:05', '2013-01-08 02:25:06', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250942, 0, 254, '00-88-65-D9-B1-8E', '2013-01-08 03:00:06', '2013-01-08 05:45:07', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009250943, 0, 254, '10-40-F3-6E-D0-FA', '2013-01-08 03:05:07', '2013-01-08 04:45:06', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251100, 0, 254, '28-CF-DA-D9-30-66', '2013-01-08 03:20:06', '2013-01-08 05:10:06', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251101, 0, 253, '00-26-AB-00-E0-1F', '2013-01-08 03:45:03', '2013-01-11 05:15:12', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251102, 0, 253, '8C-A9-82-85-EF-72', '2013-01-08 03:45:03', '2013-01-11 05:25:15', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251103, 0, 253, 'F0-CB-A1-43-A8-4D', '2013-01-08 04:05:03', '2013-01-11 05:15:09', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251104, 0, 254, '00-1B-9E-75-3E-DA', '2013-01-08 04:10:07', '2013-01-08 05:10:07', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251105, 0, 254, 'F8-7B-7A-AC-EA-D6', '2013-01-08 04:20:06', '2013-01-08 06:35:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251106, 0, 253, '90-18-7C-A9-0B-2B', '2013-01-08 04:30:03', '2013-01-11 05:15:14', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251107, 0, 253, '30-F7-C5-C5-9F-61', '2013-01-08 04:30:03', '2013-01-11 05:15:15', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251108, 0, 253, '70-DE-E2-32-F8-66', '2013-01-08 04:30:03', '2013-01-11 09:55:15', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251109, 0, 254, '00-26-C7-CD-10-9C', '2013-01-08 04:55:07', '2013-01-08 06:00:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251110, 0, 254, '5C-0A-5B-A7-54-20', '2013-01-08 05:15:07', '2013-01-08 06:15:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251111, 0, 253, '78-A3-E4-E7-0F-1C', '2013-01-08 05:20:03', '2013-01-11 05:15:06', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251112, 0, 254, '00-1B-9E-75-3E-DA', '2013-01-08 05:35:08', '2013-01-08 06:45:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251113, 0, 253, '00-1F-F3-FA-01-43', '2013-01-08 06:35:04', '2013-01-11 05:15:14', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251114, 0, 253, 'D8-B3-77-7A-C3-F2', '2013-01-08 06:40:03', '2013-01-11 05:15:13', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL),
(1009251128, 0, 254, '00-88-65-D9-B1-8E', '2013-01-08 16:25:08', '2013-01-08 17:40:05', NULL, NULL, 'passive', NULL, NULL, NULL, NULL, NULL, NULL);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/* end of provided sql fiddle data */

#Here is where we set up a table to hold expansion of all hours in rage as unix timestamp values.
DROP TABLE IF EXISTS hours_expanded;
CREATE TABLE hours_expanded
(
     id int auto_increment,
     hour_as_unix_ts int,
     full_datetime datetime NULL,
     hour_as_time time NULL,
     count_logged_in int DEFAULT 0,
     PRIMARY KEY (id)
);

#get min/max hours
SELECT @min_in := floor(unix_timestamp(min(time_login))/3600), 
    @max_out := floor(unix_timestamp(NOW())/3600)
FROM sessions_example;

#total number of rows that we will need in the `hours_expanded` table

SELECT @hours_in_sessions:= @max_out - @min_in + 1;

#dial up packet length for the string based row-generation solution (see tip of the hat)
SET @old_max_packet = @@max_allowed_packet;
SET GLOBAL max_allowed_packet = 1073741824;

#Next, we will create a long UNION ALL string that will create one row per hour needed.
#For the following concat based approach major tip of the hat to
#http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
#and Roland Bouman who is mentioned there as proposing this solution to generating rows.
SET @one_row_per_hour = (SELECT concat("INSERT INTO hours_expanded (hour_as_unix_ts) ",(repeat(concat(@s:="select 1 ","union all "),@hours_in_sessions)),"select 1;"));

#dial packet length back down
SET GLOBAL max_allowed_packet = @old_max_packet;

#make that a prepared statement via the string and execute
prepare q from @one_row_per_hour;
execute q;

#set hour_as_unix_ts column to actual UNIX TIMESTAMP hour value and full_datetime to show datetime for that hour
UPDATE hours_expanded SET hour_as_unix_ts = (SELECT @min_in * 3600 + 3600 *( id - 1)),
    full_datetime = FROM_UNIXTIME(hour_as_unix_ts),
    hour_as_time = time(full_datetime);

#get the counts and update the hours table
UPDATE hours_expanded h
INNER JOIN
(
    SELECT h.hour_as_unix_ts, COUNT(*) as the_count
    FROM hours_expanded h
    INNER JOIN sessions_example
    WHERE h.hour_as_unix_ts >= floor(unix_timestamp(time_login)/3600)*3600 and h.hour_as_unix_ts <= floor(unix_timestamp(ifnull(time_logout,now()))/3600)* 3600
    GROUP by h.hour_as_unix_ts 
) t on h.hour_as_unix_ts = t.hour_as_unix_ts SET h.count_logged_in = t.the_count;

#null out any hours that have a count of 0
UPDATE hours_expanded SET full_datetime = NULL, hour_as_time = NULL WHERE count_logged_in = 0;

#Final Select with desired output (reformat full_datetime and hour_as_time as desired)
SELECT full_datetime, hour_as_time, count_logged_in FROM hours_expanded WHERE full_datetime IS NOT NULL

Final note on the string concat/repeat.

1000000 rows of hours, which covers a period greater than 110 years, requires a 13.5M string of "SELECT 1 UNION ALL SELECT 1 . . . ", etc.

So although the approach involves using a large string to get the row expansion, the string size is not bad for the scale of the problem involved--unless you have several thousand years of session log to process :-}.

I did include a temporary increase of max_packet_size to the maximum (1G) just to ensure that no problems occur--overkill, with a large, large margin of safety. I decrease the max_packet_size back to whatever the system setting was as soon as I don't need it anymore.