Time difference in sql

Question

I created a table in mysql with 3 columns start time, end time and duration.
I used TimeDiff but it doesn’t give the correct answer.

Can someone explain how i can use start time and end time to get duration?

Answer 1

Maya,
What are values for start_time and end_time that results in negative numbers?

Are the column type of the start_time and end_time the same?

Maybe share your full query?

Answer 2

Maya since the dates are unavailable I recommend another approach. Conditionally add 24 hours to the end time if it is less than the start time. You may use an IF() function and AddTime() like this:

SELECT TimeDiff(IF(endtime>=starttime, endtime, AddTime(endtime, '24:00')), starttime)

Answer 3

Thankyou Brian, I tried this solution and it worked.

Answer 4

Could you provide a simplified sample of example data, the query and the results?

When calling TimeDiff() be certain the end time is the first field and the beginning time is the second field. It subtracts the second field from the first, so you want the higher value in the first field.

Answer 5

Maya if your time stamps cross the midnight meridian then you should include the date, too.

SELECT TimeDiff(endDate+' '+endTime, startDate+' '+startTime)

Answer 6

Maya,
Please consider the use of datetime also. There are flight trips taking hours such that the trip can span between two dates e.g. if the flight took 6 hours and begin at 19:00.

Inclusion of date information apart from time clarifies such information where flight trip spans between 2 dates.

Answer 7

I put it as duration = TimeDiff(endtime, starttime)

Answer 8

and times are in 24 hours
so in one row i had start time 15:30 and endtime 00:30 which is 9 hours difference but i got 14:00

Answer 9

and some answers are negative