CODEWITHSUNDEEP
mysqltime
M a m a D
M a m a D

Reputation: 2139

Mysql: calculate length time between two times in AM and PM

I need to calculate the difference between two columns which are of type time. The fields are named start_time and end_time. I use the function timediff(s,e) and it works well but some times it doesn't. For example when end_time is 00:00 which is considered as 12 AM and start_time is 19:00 which is 7 PM the difference function shows 19 hours (actually I also select hour(timediff(s,e))) while I expect it to be 5 hours.

How can I fix this?

I tried this

case 
    when finish_time < start_time then TIMEDIFF(timestamp('2021:01:02',finish_time),timestamp('2021:01:01',start_time))
    else TIMEDIFF(timestamp('2021:01:01',finish_time),timestamp('2021:01:01',start_time))
end as diff,

and it works

Upvotes: 0

Views: 375

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94672

As 00:00:00 is considered the start of a new day or even if you have an end time that is the day after the start time you will have to make your start and end field into DATETIME types. Also if you want the difference between the 2 times you should be using timediff(end,start)

Upvotes: 1

Related Questions