MySQL-如何求和? [英] MySQL - How to SUM times?

问题描述

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

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.

推荐答案

尝试查看 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-如何求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！