Converting days to seconds in MySQL

Question

I need to convert the following column into seconds:

+----------------------+
| duration             |
+----------------------+
| 00:15:52.22          |
| 01:15:51.65          |
| 02:15:50.93          |
| 27 days, 06:22:41.11 |
| 27 days, 07:22:40.76 |
| 27 days, 08:22:40.43 |
+----------------------+

The problem is that the TIME_TO_SEC() function works correctly only for the first three rows returning the following result:

+-----------------------+
| TIME_TO_SEC(duration) |
+-----------------------+
|            952.220000 |
|           4551.650000 |
|           8150.930000 |
|             27.000000 |
|             27.000000 |
|             27.000000 |
+-----------------------+

Is there a function I can use that would convert all the rows into seconds?

Answer 1

The following answer assumes that every entry in the duration column takes the form of a time component only, or of some number of days followed by a time component. Assuming this, we can use a CASE expression to selectively parse either type of data. In the case there is a days component, we can convert to seconds and add this to the number of seconds from the time component.

SELECT
    CASE WHEN duration LIKE '%days%'
         THEN CAST(SUBSTRING_INDEX(duration, ' ', 1) AS UNSIGNED) * 24*60*60 +
              TIME_TO_SEC(SUBSTRING_INDEX(duration, ' ', -1))
         ELSE TIME_TO_SEC(duration)
    END AS duration_in_seconds
FROM yourTable

Output:

Demo here:

Rextester