Sum hours worked Mysql

Question

I have records of a truck’s route on a chart.

===================================================

This example up there I have 5 records I picked up in the table checking Speed > 0 The records from 1 to 3 we realized that it is sequential. Then there was a pause and the truck went back to walk 2 hours later.

If I add the time in the SQL query it will give that it worked 1 day, 2 hours and a few seconds. When it was actually 30 seconds (1 to 3). E had 2 more Tarts day 30( nº 4) and day 31 (nº 5).

How do I get the total amount of hours or seconds worked ? Note the table has more than 500 thousand records. I wanted to take in the period of 1 week the amount of

Answer 1

You can group by formatted date and then use the function UNIX_TIMESTAMP to get the difference from the lowest date to the highest date in seconds:

SELECT caminhao,
       DATE_FORMAT(data, '%d/%m/%Y') AS data,
       UNIX_TIMESTAMP(MAX(data)) - UNIX_TIMESTAMP(MIN(data)) AS segundos
  FROM tabela t
 GROUP BY caminhao, DATE_FORMAT(data, '%d/%m/%Y')
 ORDER BY caminhao, data;