gman — 2012-08-01T23:22:26-04:00 — #1
I'm using MySQL version 5.5.25a
I need to get the total time difference from startTime and endTime
So if I have
startTime = 14:00
endTime = 15:30
I need the result to show as 1.5
I believe I am on the right track just not sure how to show the result as I need it...
The fields are TIMESTAMP
TIME_FORMAT(SEC_TO_TIME(UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(startTime)), '%H.%i') AS totalTime
Right now my result would be 01.30
r937 — 2012-08-02T02:20:19-04:00 — #2
SELECT ROUND(( TIME_TO_SEC(endtime) -
TIME_TO_SEC(starttime) ) / 3600.0 , 2 ) AS hours_diff
gman — 2012-08-02T19:35:14-04:00 — #3
Thanks Rudy, works great.
However I failed to mention this when I posted, what do you think is the best way to do this?
If my timestamp field for startTime is:
and my endTime field is:
What would be the way to have MySQL calculate the time difference between the 2 days, because right now if I use the query it returns: -0.98 instead of 28.0
Thanks again and sorry for not posting that vital info...
gman — 2012-08-02T20:41:21-04:00 — #4
OK, for some reason it's working now... lol