Q1:为什么数据库没有返回这两个日期的平均值的有效值?
A:返回的值是预期的,它是明确定义的 MySQL 行为。
MySQL 自动将日期或时间值转换为number如果该值用于数字上下文反之亦然。
MySQL参考手册:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
在 MySQL 中,AVG
聚合函数运行于numeric values.
在 MySQL 中,一个DATE
or DATETIME
表达式可以在a中求值numeric语境。
作为一个简单的演示,执行numeric对 a 进行加法运算DATETIME
将日期时间值隐式转换为数字。这个查询:
SELECT NOW(), NOW()+0
返回如下结果:
NOW() NOW()+0
------------------- -----------------------
2015-06-23 17:57:48 20150623175748.000000
请注意,表达式的返回值NOW()+0
is not a DATETIME
, 它是number.
当您指定一个SUM()
or AVG()
函数在一个DATETIME
表达式,相当于转换DATETIME
化为一个数,然后求和
或对数字进行平均。
也就是说,这个表达式的返回AVG(mydatetimecol)
等价于该表达式的返回:AVG(mydatetimecol+0)
被“平均”的是一个数值。您已经观察到,返回的值不是有效的日期时间;即使它碰巧看起来像有效的日期时间,它也可能不是您认为真正的“平均值”的值。
Q2:如果上述方式失败,如何得到该字段的实际平均值?
A2:一种方法是将日期时间转换为可以“准确”平均的数值,然后将其转换回日期时间。
例如,您可以将日期时间转换为表示秒数从某个固定的时间点开始,例如
TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)
然后,您可以对这些值进行“平均”,以获得平均值秒数从某个固定的时间点开始。 (注意:要注意加起来的行数极多,数值极大,超过限制(最大数值),数值溢出问题。)
AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date))
要将其转换回日期时间,请将该值添加为秒数回到固定时间点:
'2015-01-01' + INTERVAL AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)) SECOND
(请注意,DATEIME
值在 MySQL 会话的时区中进行评估;所以存在边缘情况,其中的设置time_zone
MySQL会话中的变量会对返回值产生一些影响。)
MySQL还提供了一个UNIX_TIMESTAMP()
函数返回一个 Unix 风格的整数值,即从纪元开始(UTC 时间 1970 年 1 月 1 日午夜)起的秒数。您可以使用它来更简洁地完成相同的操作:
FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(t.my_date)))
请注意,这个最终表达式实际上在做同样的事情...将日期时间值转换为自 '1970-01-01 00:00:00' UTC 以来的秒数,取其数值平均值,然后添加该平均值返回到 '1970-01-01' UTC 的秒数,最后将其转换回DATETIME
值,在当前会话中表示time_zone
.
Q3:Django DateTimeField 没有设置来处理平均吗?
A:显然,Django 的作者对 SQL 表达式从数据库返回的值感到满意AVG(datetime)
.