Thursday 9 October 2014

Calculate a Sum of Time using MySQL

Calculate a Sum of Time using MySQL

We have Following Table Structure of myvideos

CREATE TABLE IF NOT EXISTS `myvideos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='3' AUTO_INCREMENT=5 ;


INSERT INTO `myvideos` (`id`, `name`, `time`) VALUES
(1, 'Video 1', '02:02:00'),
(2, 'Video  2', '02:02:00'),
(3, 'Video  3', '02:02:00'),
(4, 'Video  4', '02:04:00');


Problem: How to calculate the sum of time of video?


Solution:
select sec_to_time(sum(time_to_sec(time))) as total_time from myvideos