How can I get the median time of the times returned from this query in MySQL?

Question

I have the following query..

SELECT 
    TIME(date_time)
FROM 
    login_attempts 
WHERE 
    DATE_FORMAT(DATE(date_time), '%d-%m-%Y') = DATE_FORMAT('2016-08-09', '%d-%m-%Y') 
AND login_successful = 1

Which returns the following times from the database..

TIME(date_time)
---------------
09:08:14
09:08:36
10:08:12
10:08:29
10:08:39
11:08:52
11:08:54

How can I get the median time of the times returned from this query?

EDIT - Here is the information to build a table in SQLFiddle.

CREATE TABLE `login_attempts` (
  `id` int(11) UNSIGNED NOT NULL,
  `username` varchar(100) NOT NULL,
  `ip_address` varbinary(128) NOT NULL,
  `date_time` datetime DEFAULT NULL,
  `login_successful` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `login_attempts` (`id`, `username`, `ip_address`, `date_time`, `login_successful`) VALUES
(1, 'test@test.com', '127.0.0.1', now(), 1),
(2, 'test', '127.0.0.1', now(), 0),
(3, 'test@test.com', '127.0.0.1', now(), 1),
(4, 'test', '127.0.0.1', now(), 0),
(5, ' test@test.com', '127.0.0.1', now(), 1);

ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `login_attempts`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Please create DDLs for data in SQLFiddle so people can better help you instead of spending time in creating data. — Jitesh Sojitra, Aug 23 '16 at 04:15

score 1 · Accepted Answer · answered Aug 23 '16 at 04:42

You can get the median time value in the following way:

SELECT 
TIME_FORMAT(SEC_TO_TIME(AVG(TIME_TO_SEC(timeTable.only_time))),'%H:%i:%s') AS medianTime
FROM 
(
    SELECT 
        TIME(date_time) only_time,
        @rn := @rn + 1 AS row_number
    FROM 
        login_attempts 
    CROSS JOIN (SELECT @rn := 0) var
    WHERE 
        DATE_FORMAT(DATE(date_time), '%d-%m-%Y') = DATE_FORMAT('2016-08-09', '%d-%m-%Y')    AND 
        login_successful = 1
    ORDER BY only_time
) AS timeTable

CROSS JOIN 
(
    SELECT 
        COUNT(*) total_rows
    FROM 
        login_attempts 
    WHERE 
        DATE_FORMAT(DATE(date_time), '%d-%m-%Y') = DATE_FORMAT('2016-08-09', '%d-%m-%Y') AND 
        login_successful = 1
) AS t
WHERE timeTable.row_number IN ( floor((total_rows+1)/2), floor((total_rows+2)/2))

See Demo

How can I get the median time of the times returned from this query in MySQL?

1 Answers1