Add hours worked Mysql

Question

Add hours worked Mysql

Navigation

#1 by (0 votes)

0

I have track records of a truck in a table.

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

This example up there I have 5 records that I picked up in the table by checking Speed > 0 Records 1 through 3 show that it is sequential. There was a pause and the truck returned 2 hours later.

If I add up the time in the SQL query it will give that it worked 1 day, 2 hours and a few seconds. When in fact it was 30 seconds (1 to 3). And had 2 more starts on the 30th (nº 4) and the 31st on the 5th.

How do I get the total amount of hours or seconds worked? Note The table has more than 500,000 records. I wanted to get the amount of

php mysql sql

asked by anonymous 06.06.2017 / 01:28

1 answer

Update information received from arduino How to run my App in multiple platforms

score 0 · Accepted Answer

You can group by formatted date and then use the UNIX_TIMESTAMP to get the difference from the smallest date to the largest 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;