Hospital Llano - 2 months ago 8

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.

Source (Stackoverflow)

Comments