问题描述
我有一个表，其中包含以下格式的日期时间值:
I have a table that contains date-time values in this format:
开始
1/13/2009 7:00:00 AM
1/13/2009 7:00:00AM
END
1/13/2008 2:57:00 PM
1/13/2008 2:57:00PM
我使用"str to date"功能将其转换为日期时间格式.
I use the 'str to date' function to convert these into a date-time format.
如何计算它们之间的差异?然后汇总所有内容，以便显示总时数(即一周的总时数为40:53).
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).
我正在尝试timediff函数，但结果不总和.
I was trying the timediff function but the results don't sum.
推荐答案
尝试查看 UNIX_TIMESTAMP 和 SEC_TO_TIME .
Try looking into UNIX_TIMESTAMP and SEC_TO_TIME.
您将总结时间戳之间的差异，然后使用该值(将以毫秒为单位)来获取时间:
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
)
这篇关于MySQL-如何求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！