MySQL Tutorial - How to calculate time correctly

A database server has a database called "Cinema" which has a table called "Movies". This table has next fields:

• ID
• Name
• Duration

Hereafter, you can see table 'movies':

movies_id	movies_name	movies_duration
1	Movie I	02:23:34
2	Movie II	01:44:56
3	Movie III	02:56:34

To calculate duration of all movies, we could write:

SELECT sum(movies_duration) FROM movies;

But it is wrong!

Result:

sum(movies_duration)
62424

To fix this problem, it is necessary convert time to seconds with next SQL statement:

SELECT time_to_sec(movies_duration) FROM movies;

Result:

time_to_sec(movies_duration)
8614
6296
10594

And next, use function SUM() to calculate duration of all movies

SELECT sum(time_to_sec(movies_duration)) FROM movies;

Result:

sum(time_to_sec(movies_duration))
25504

Finally, convert the sum (in seconds) to time using the next statement:

SELECT sec_to_time(sum(time_to_sec(movies_duration))) FROM movies;

Result:

sec_to_time(sum(time_to_sec(movies_duration)))
07:05:04

Thus you can calculate time correctly using three MySQL functions:

1. TIME_TO_SEC(...)
2. SUM(...)
3. SEC_TO_TIME(...)