问题描述
我有一个包含以下格式的日期时间值的表格:
I have a table that contains date-time values in this format:
开始
2009 年 1 月 13 日上午 7:00:00
1/13/2009 7:00:00AM
结束
1/13/2008 2:57:00PM
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.
您可以总结时间戳之间的差异，然后使用该值(以毫秒为单位)来获取时间:
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 - 如何求和时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！