如何在 MySQL 中平均日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1021947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I make an average of dates in MySQL?
提问by fmsf
How can I make an average between dates in MySQL? I am more interested in the time values, hours and minutes.
如何在 MySQL 中计算日期之间的平均值？我对时间值、小时和分钟更感兴趣。
On a table with:
在一张桌子上：
| date_one | datetime |
| date_two | datetime |
Doing a query like:
做一个查询，如：
SELECT AVG(date_one-date_two) FROM some_table WHERE some-restriction-applies;
Edit:
编辑：
The AVG(date1-date2)works but I have no clue what data it is returning.
的AVG(date1-date2)作品，但我不知道它是什么返回数据。
回答by gahooa
This seems a bit hackish, but will work for dates beteen ~ 1970 and 2030 (on 32 bit arch). You are essentially converting the datetime values to integer, averaging them, and converting the average back to a datetime value.
这似乎有点骇人听闻，但适用于 1970 年和 2030 年之间的日期（在 32 位架构上）。您实际上是将日期时间值转换为整数，对它们求平均值，然后将平均值转换回日期时间值。
SELECT
from_unixtime(
avg(
unix_timestamp(date_one)-unix_timestamp(date_two)
)
)
FROM
some_table
WHERE
some-restriction-applies
There is likely a better solution out there, but this will get you by in a pinch.
可能有更好的解决方案，但这会让你陷入困境。
回答by Mysql Guru
select avg(datediff(date1,date2))
select avg(timediff(datetime,datetime))
回答by julitillo
SELECT TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, '2011-02-12 10:00:00', '2011-02-12 12:00:00')/2, '2011-02-12 10:00:00')
The result is '2011-02-12 11:00:00'
结果是'2011-02-12 11:00:00'
回答by Alex Martelli
SELECT date_one + (date_two - date_one) / 2 AS average_date
FROM thetable
WHERE whatever
You can't sum dates, but you can subtract them and get a time interval that you can halve and add back to the first date.
你不能对日期求和，但你可以减去它们并得到一个时间间隔，你可以将其减半并加回到第一个日期。
回答by Martin
CREATE TABLE `some_table`
(
`some_table_key` INT(11) NOT NULL AUTO_INCREMENT,
`group_name` VARCHAR(128) NOT NULL,
`start` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`finish` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`some_table_key`)
);
SELECT
group_name,
COUNT(*) AS entries,
SEC_TO_TIME( AVG( TIME_TO_SEC( TIMEDIFF(finish, start) ) ) ) AS average_time
FROM some_table
GROUP BY
some_table.group_name
;
You should always specify the group you want when using group functions, you can end up in some nasty messes with the group functions if you later extend queries with JOIN etc and assume MySql will choose the right group for you.
在使用组函数时，您应该始终指定所需的组，如果您稍后使用 JOIN 等扩展查询并假设 MySql 会为您选择正确的组，则最终可能会对组函数造成一些令人讨厌的混乱。
回答by ChickenMilkBomb
thinking outloud you could do a datediff in minutes from a set time, average that and then add those minutes back to the set time...
大声思考你可以在设定时间的几分钟内做一个 datediff ，平均然后将这些分钟加回设定时间......
回答by Paul A Jungwirth
AVG is a grouping function, which means it will sum all the rows in the table and divide by the row count. But it sounds like you want the average of two different columns, reported individually for each row. In that case, you should just compute it yourself: (date1+date2)/2. (MySQL may want some extra syntax to add those columns properly.)
AVG 是一个分组函数，这意味着它将对表中的所有行求和并除以行数。但听起来您想要两个不同列的平均值，为每一行单独报告。在这种情况下，您应该自己计算：(date1+date2)/2。（MySQL 可能需要一些额外的语法来正确添加这些列。）
The code you've written will give you the table's average elapsedtime between date1 and date2.
您编写的代码将为您提供表在 date1 和 date2 之间的平均经过时间。
