I have a table that contains date-time values in this format:
START
1/13/2009 7:00:00AM
END
1/13/2008 2:57:00PM
I use the ‘str to date’ function to convert these into a date-time format.
How do I calculate a difference between them?
And then sum it all up so that it displays in total hours (ie total hours for week is 40:53).
I was trying the timediff function but the results don’t sum.
解决方案
You would sum up the differences between the timestamps, then use that value (would be in milliseconds) to get the time:
SELECT SEC_TO_TIME(time_milis / 1000)
FROM (
SELECT SUM(UNIX_TIMESTAMP(date1) – UNIX_TIMESTAMP(date2)) as time_milis
FROM table
)