[adsense id=”0514458240″ width=”468″ height=”60″]
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?
Setup
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)
Query
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)