mysql timediff no proper output when endtime is 24

问题:

I'm using datatype time to calculate the class taken timings. For calculation I use TIMEDIFF(endtime,starttime).

Query

SELECT TIMEDIFF('00:26:08','21:58:18') FROM students_session WHERE id='#'

I'm not getting the proper o/p which is 02:27:50. Instead I get -21:32:10, which is wrong.

How to rectify this?

回答1:

The issue is that you know that '00:26:08' is after '21:58:18' (following morning), but MySQL is not aware, thus the result is correct from MySQL point of view.

You either need to provide a date part, where the end_date falls to the next day, or you need to add 24 hours (1 day) to the end_date. These will tell MySQL that the end_date is greater than the start_date and you will get the results you expect.

SELECT TIMEDIFF(timeadd('00:26:08','24:00:00'),'21:58:18') from students_session where id='#'