如何在MySQL中平均日期? [英] How can I make an average of dates in MySQL?
问题描述
如何在MySQL中计算日期之间的平均值? 我对时间值,小时和分钟更感兴趣.
How can I make an average between dates in MySQL? I am more interested in the time values, hours and minutes.
在具有以下内容的桌子上
On a table with:
| date_one | datetime |
| date_two | datetime |
进行如下查询:
SELECT AVG(date_one-date_two) FROM some_table WHERE some-restriction-applies;
AVG(date1-date2)
可以工作,但是我不知道它正在返回什么数据.
The AVG(date1-date2)
works but I have no clue what data it is returning.
推荐答案
这似乎有些骇人听闻,但它适用于1970年〜1970年和2030年之间的日期(在32位拱上).您实际上是将日期时间值转换为整数,对其求平均,然后将平均值转换回日期时间值.
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.
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.
这篇关于如何在MySQL中平均日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!