0

Time difference in sql

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?

1st Dec 2020, 3:23 AM
Maya
Maya - avatar
9 Respuestas
+ 2
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?
1st Dec 2020, 3:07 PM
Ipang
+ 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)
1st Dec 2020, 11:27 PM
Brian
Brian - avatar
+ 2
Thankyou Brian, I tried this solution and it worked.
2nd Dec 2020, 9:59 PM
Maya
Maya - avatar
+ 1
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.
1st Dec 2020, 4:12 AM
Brian
Brian - avatar
+ 1
Maya if your time stamps cross the midnight meridian then you should include the date, too. SELECT TimeDiff(endDate+' '+endTime, startDate+' '+startTime)
1st Dec 2020, 4:38 PM
Brian
Brian - avatar
+ 1
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.
1st Dec 2020, 11:04 PM
Ipang
0
I put it as duration = TimeDiff(endtime, starttime)
1st Dec 2020, 12:13 PM
Maya
Maya - avatar
0
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
1st Dec 2020, 12:15 PM
Maya
Maya - avatar
0
and some answers are negative
1st Dec 2020, 12:16 PM
Maya
Maya - avatar