Calculate time difference in mysql in case it is midnight-CodePudding

I am calculating the time difference as follows:

SELECT SUBTIME(DATE_FORMAT(`DataChegada`,'%H:%i'), DATE_FORMAT(`Datasaida`,'%H:%i')) AS `Horas Consumidas` FROM raddb.RegistoAcompa

The problem is that I have the following situation:

Datasaida = '2022-07-13 21:00:00' DataChegada = '2022-07-14 00:00:00'

The time difference is 3 hours but it is returning '-21:00:00' hours. How can I solve the problem?

CodePudding user response：

Since both columns are datetimes you should use TIMEDIFF():

SELECT TIMEDIFF(`DataChegada`, `Datasaida`) AS `Horas Consumidas` 
FROM raddb.RegistoAcompa