Mysql Group concat and subtraction values

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

What `WITH SUBTRACTIONS` means? What does it mean from the domain logic perspective? — zerkms, Sep 28 '14 at 03:51
With Datediff or Timediff, for this case to obtain calculates (00:00:14 - 00:00:02 ).... except first, like example — Hospital Llano, Sep 28 '14 at 04:07
what if the first value is greater? Will it be a negative interval? What if there are 3 laps? Why do you subtract `14 - 2` not the other way around? — zerkms, Sep 28 '14 at 04:08
What I am trying to get are positive times, subtracting from right to left , and to obtain the difference , which would be the time for each lap — Hospital Llano, Sep 28 '14 at 04:22
I don't understand 1 & 2 in the result. Perhaps the data set should be 9,9,10? — Strawberry, Sep 28 '14 at 07:12

score 1 · Accepted Answer · answered Sep 28 '14 at 07:33

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.

An SQLfiddle to test with.

Thanks Joachim ¡¡¡ is the solution: Thank you master – Hospital Llano Oct 01 '14 at 23:32 — Hospital Llano, Oct 01 '14 at 23:32

Mysql Group concat and subtraction values

1 Answers1