mysql get all intervals of datetime based on interval hour

Question

I have the following table with datetime type as follows

Id            interval_time                      processed_time
1             2016-07-22 08:00:00                 2016-07-22 09:25:00
2            2016-07-22 09:00:00                   2016-07-22 10:30:00
3            2016-07-22 14:00:00                   2016-07-22 15:23:00
4            2016-07-22 15:00:00                   2016-07-22 14:27:00
5            2016-07-22 16:00:00                   2016-07-22 15:24:00
6            2016-07-22 17:00:00                   2016-07-22 16:40:00

I need to get the all missed interval_time for last 24 hours with the given input time.

I need to get all missed intervals which doesn't exists in table as follows.

2016-07-22 10:00:00
2016-07-22 11:00:00
2016-07-22 12:00:00
2016-07-22 13:00:00

here i can join the table with the list of all intervals of last 24 hours , but how can i get the last 24 hours list with hour as interval.

Answer 1

Assume '2016-07-22 22:19:00' be your requested datetime.Then you can use the last 24hr status using the query as following.

DB STRUCTURE

CREATE TABLE IF NOT EXISTS `mytable` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `interval_time` datetime NOT NULL,
  `processed_time` datetime NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `mytable`
--

INSERT INTO `mytable` (`Id`, `interval_time`, `processed_time`) VALUES
(1, '2016-07-22 08:00:00', '2016-07-22 09:25:00'),
(2, '2016-07-22 09:00:00', '2016-07-22 10:30:00'),
(3, '2016-07-22 14:00:00', '2016-07-22 15:23:00'),
(4, '2016-07-22 15:00:00', '2016-07-22 14:27:00'),
(5, '2016-07-22 16:00:00', '2016-07-22 15:24:00'),
(6, '2016-07-22 17:00:00', '2016-07-22 16:40:00');

QUERY

SELECT A.mydate FROM(    
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 1 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 2 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 3 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 4 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 5 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 6 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 7 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 8 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 9 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 10 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 11 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 12 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 13 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 14 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 15 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 16 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 17 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 18 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 19 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 20 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 21 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 22 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 23 HOUR,"%Y-%m-%d %H:00:00") mydate
UNION
SELECT DATE_FORMAT('2016-07-22 22:19:00' - INTERVAL 24 HOUR,"%Y-%m-%d %H:00:00") mydate
) A LEFT JOIN `mytable` B ON A.mydate = B.`interval_time` WHERE B.Id IS NULL ORDER BY mydate DESC;

Check SQL Fiddle

To reduce complication of above query, you can use stored procedure call as follows..

DELIMITER $$

CREATE PROCEDURE `LastHrs`(IN indate DATETIME)

BEGIN

 DECLARE X  INT;
 DECLARE str TEXT;

 SET X = 2;
 SET str =  CONCAT('SELECT DATE_FORMAT("',indate,'" - INTERVAL 1 HOUR,"%Y-%m-%d %H:00:00") mydate'); 

 WHILE X  <= 24 DO
 SET  str = CONCAT(str,' UNION ','SELECT DATE_FORMAT("',indate,'" - INTERVAL ',X,' HOUR,"%Y-%m-%d %H:00:00") mydate');
 SET  X = X + 1; 
 END WHILE; 

SET @query = CONCAT('SELECT A.mydate FROM(',str,') A LEFT JOIN `mytable` B ON A.mydate = B.`interval_time` WHERE B.Id IS NULL ORDER BY mydate DESC');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 

END$$
DELIMITER ;

And you can call stored procedure using query as

CALL LastHrs('2016-07-22 22:19:00');