Hospital Llano - 8 months ago 46

MySQL Question

I tried to calculate the difference in values obtained from a mysql query where data values are extracted in TIME format time laps of competition . the goal is to get the lap times between arrivals for each lap. This is the table

`Times`

`id number time`

---------+----------------+--------------------

1 9 00:00:02.000000

2 10 00:00:14.000000

3 9 00:11:09.000000

Mysql

`SELECT`

t.number, count(*) as laps,

group_concat(time order by t.id separator ',' ) as times,

SEC_TO_TIME(SUM(TIME_TO_SEC(`time`))) as total

FROM

times t

GROUP BY

number

ORDER BY

laps DESC, total ASC

The output of this query is:

`number laps times total`

--------------------------------------------------------------

9 2 00:00:02,00:00:14 00:00:16.000000

10 1 00:11:09 00:11:09.000000

Now, what I need is to get the length of time of each lap, WITH SUBTRACTIONS.

Any suggestion please. Thanks to this result:

`number laps times total`

--------------------------------------------------------------

9 2 00:00:02,00:00:12 00:00:14.000000

10 1 00:11:09 00:11:09.000000

Answer

Seems you have total times stored and want to calculate lap times. One way to do this is using a subquery;

```
SELECT t.number, COUNT(1) laps,
GROUP_CONCAT(SEC_TO_TIME(time) ORDER BY t.id) times,
SEC_TO_TIME(SUM(time)) total
FROM (
SELECT t1.id, t1.number,
TIME_TO_SEC(t1.time) - COALESCE(SUM(TIME_TO_SEC(t2.time)), 0) time
FROM times t1
LEFT JOIN times t2 ON t1.number = t2.number AND t2.id < t1.id
GROUP BY t1.id, t1.number, t1.time
) t
GROUP BY number
```

The subquery calculates the lap times by subtracting all times for the same number with a smaller id from the total time for the end of each lap. The outer query does the formatting of the lap times in the format you're looking for.