MySQL Support

MySQL – how to generate a timeline based on duration

February 6, 2015  |  Posted by admin |  Comments Closed


Imagine you have you a catalog of bands and their music. Each music contains information about the song and the duration of the song. An SQL has been written which returns the rows in a particular order but you now want to have these create a timeline. In other words if we have 3 songs, one of duration 1:30, the other of duration 1:45 and the final one of 1:34. If we start the process at 12:00 at what time will 1st song start and end, the 2nd song start and end and the 3rd start and end?

This paper shows how this is possible in MySQL with a single SQL query.

mysql> create table songs(song varchar(100), duration int, sort_order int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into songs(song, duration, sort_order) 
    -> values ('Row, row your boat', 94, 1),
    -> ('Humpty dumpty', 76, 2),
    -> ('Old McDonalds Farm', 100, 3),
    -> ('Goodnight', 63, 4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from songs;
+--------------------+----------+------------+
| song               | duration | sort_order |
+--------------------+----------+------------+
| Row, row your boat |       94 |          1 |
| Humpty dumpty      |       76 |          2 |
| Old McDonalds Farm |      100 |          3 |
| Goodnight          |       63 |          4 |
+--------------------+----------+------------+
4 rows in set (0.01 sec)

Now we want the songs to start at 13:05 and we will play the songs in the sort_order. The duration is given in seconds. We want this to return a timeline i.e. the start and end times of each song if they were played one after another.

The MySQL functions of importance in this are addtime and sec_to_time which converts the seconds into a time (format hh:mm:ss).

mysql> select song, 
    -> @starttime, 
    -> @starttime:=addtime(@starttime, sec_to_time(duration)) endtime, 
    -> duration, 
    -> sort_order 
    -> from songs 
    -> order by sort_order;
+--------------------+------------+----------+----------+------------+
| song               | @starttime | endtime  | duration | sort_order |
+--------------------+------------+----------+----------+------------+
| Row, row your boat | 13:00:00   | 13:01:34 |       94 |          1 |
| Humpty dumpty      | 13:01:34   | 13:02:50 |       76 |          2 |
| Old McDonalds Farm | 13:02:50   | 13:04:30 |      100 |          3 |
| Goodnight          | 13:04:30   | 13:05:33 |       63 |          4 |
+--------------------+------------+----------+----------+------------+
4 rows in set (0.00 sec)

Let us imagine we want to include a delay of 5 seconds between the playing of each song. This can be easily done by calculating the start time after the end time has been calculated by adding in the additional 5 seconds.

mysql> select song, 
    -> @starttime startime, 
    -> @starttime:=addtime(@starttime, sec_to_time(duration)) endtime, 
    -> @starttime:=addtime(@starttime, @delay) with_delay, 
    -> duration, 
    -> sort_order 
    -> from songs, 
    -> (select @starttime:='13:00:00', @delay:='00:00:05') x 
    -> order by sort_order;
+--------------------+----------+----------+------------+----------+------------+
| song               | startime | endtime  | with_delay | duration | sort_order |
+--------------------+----------+----------+------------+----------+------------+
| Row, row your boat | 13:00:00 | 13:01:34 | 13:01:39   |       94 |          1 |
| Humpty dumpty      | 13:01:39 | 13:02:55 | 13:03:00   |       76 |          2 |
| Old McDonalds Farm | 13:03:00 | 13:04:40 | 13:04:45   |      100 |          3 |
| Goodnight          | 13:04:45 | 13:05:48 | 13:05:53   |       63 |          4 |
+--------------------+----------+----------+------------+----------+------------+
4 rows in set (0.01 sec)

Tags: , , ,