How do I subtract and display the result in the MYSQL query

Question

I have 3 tables

Vehicles

idVeiculo | Placa | Prefixo

oleomotor

idMotor | idVeiculo | datatroca | kmtroca | horimetroca | proximatroca |idMecanico

I already have this query below that shows the result of the last changes of each vehicle without repeating the vehicle. how do I include in this query the table hodomentro that will take the last hour of each vehicle and subtract with the proximatroca and give the result of how many hours left.

SELECT DISTINCT b.prefixo as idVeiculo, max(datatroca) as datatroca, max(kmtroca) as kmtroca, max(horimetroca) as horimetroca, max(proximatroca) as proximatroca, idMecanico FROM oleomotor a
                  JOIN veiculos b on (a.idVeiculo=b.idVeiculo)
                  GROUP BY a.idVeiculo order by datatroca DESC;

Hodometre

idVeiculo | km | horimetro

Answer 1

You can use the Function TIMEDIFF() which aims at returns the difference between two time values (expressed as a time value).

Add: timediff(proximatroca, max(horimetro)) as Horas Restantes

Would look like this:

SELECT DISTINCT b.prefixo as idVeiculo,
                max(datatroca) as datatroca,
                max(kmtroca) as kmtroca,
                max(horimetroca) as horimetroca,
                max(proximatroca) as proximatroca,
                idMecanico,
                timediff(proximatroca, max(horimetro)) as 'Horas Restantes'
FROM oleomotor a
JOIN veiculos b on (a.idVeiculo=b.idVeiculo) 
JOIN hodometro h on (a.idVeiculo= h.idVeiculo) 
GROUP BY a.idVeiculo 
ORDER BY datatroca DESC;