MySQL - 如何求和时间?

2021-11-20 00:00:00 mysql

我有一个包含以下格式的日期时间值的表格:

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
)

相关文章