问题描述
我在 MySQL(实际上是 MariaDB)数据库上运行 Django 应用程序.
I'm running a Django application on top of a MySQL (actually MariaDB) database.
我的 Django 模型如下所示:
My Django Model looks like this:
from django.db import models
from django.db.models import Avg, Max, Min, Count
class myModel(models.Model):
my_string = models.CharField(max_length=32,)
my_date = models.DateTimeField()
@staticmethod
def get_stats():
logger.info(myModel.objects.values('my_string').annotate(
count=Count("my_string"),
min=Min('my_date'),
max=Max('my_date'),
avg=Avg('my_date'),
)
)
当我运行 get_stats() 时，我得到以下日志行:
When I run get_stats(), I get the following log line:
[2015-06-21 09:45:40] INFO [all_logs:96] [{'my_string': u'A', 'count': 2, 'avg': 20080507582679.5, 'min': datetime.datetime(2007, 8, 2, 11, 33, 53, tzinfo=<UTC>), 'max': datetime.datetime(2009, 2, 13, 5, 20, 6, tzinfo=<UTC>)}]
我遇到的问题是数据库返回的 my_date 字段的平均值是:20080507582679.5.仔细看看那个数字.这是无效的日期格式.
The problem I have with this is that the average of the my_date field returned by the database is: 20080507582679.5. Look carefully at that number. It is an invalid date format.
为什么数据库没有为这两个日期的平均值返回一个有效值?如果描述的方式失败，我如何获得该字段的实际平均值?Django DateTimeField 是否未设置为处理平均?
Why doesn't the database return a valid value for the average of these two dates? How do I get the actual average of this field if the way described fails? Is Django DateTimeField not setup to do handle averaging?
推荐答案
Q1:为什么数据库没有返回这两个日期的平均值的有效值?
答:返回的值是预期的，它是明确定义的 MySQL 行为.
A: The value returned is expected, it's well defined MySQL behavior.
如果在数字上下文中使用日期或时间值，MySQL会自动将日期或时间值转换为数字，反之亦然.
MySQL automatically converts a date or time value to a number if the value is used in a numeric context and vice versa.
MySQL 参考手册:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
MySQL Reference Manual: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
在 MySQL 中，AVG 聚合函数对 numeric 值进行操作.
In MySQL, the AVG aggregate function operates on numeric values.
在 MySQL 中，DATE 或 DATETIME 表达式可以在 numeric 上下文.
In MySQL, a DATE or DATETIME expression can be evaluated in a numeric context.
作为一个简单的演示，对 DATETIME 执行 numeric 加法运算会将日期时间值隐式转换为数字.这个查询:
As a simple demonstration, performing an numeric addition operation on a DATETIME implicitly converts the datetime value into a number. This query:
SELECT NOW(), NOW()+0
返回如下结果:
NOW() NOW()+0
------------------- -----------------------
2015-06-23 17:57:48 20150623175748.000000
请注意，表达式 NOW()+0 的返回值不是 DATETIME，它是一个数字.
Note that the value returned for the expression NOW()+0 is not a DATETIME, it's a number.
当您在 DATETIME 表达式上指定 SUM() 或 AVG() 函数时，这相当于转换 DATETIME 变成一个数字，然后求和或取平均值.
When you specify a SUM() or AVG() function on a DATETIME expression, that's equivalent to converting the DATETIME into a number, and then summing
or averaging the number.
也就是说，这个表达式AVG(mydatetimecol)的返回等价于这个表达式的返回:AVG(mydatetimecol+0)
That is, the return from this expression AVG(mydatetimecol) is equivalent to the return from this expression: AVG(mydatetimecol+0)
平均"是一个数值.而且您已经观察到，返回的值不是有效的日期时间；即使在它看起来像一个有效的日期时间的情况下，它也可能不是一个你会认为是真正的平均值"的值.
What is being "averaged" is a numeric value. And you have observed, the value returned is not a valid datetime; and even in cases where it happens to look like a valid datetime, it's likely not a value you would consider a true "average".
Q2:如果描述的方式失败，我如何获得该字段的实际平均值?
A2:一种方法是将日期时间转换为可以准确"平均的数值，然后将其转换回日期时间.
A2: One way to do that is to convert the datetime into a numeric value that can be "accurately" averaged, and then convert that back into a datetime.
例如，您可以将日期时间转换为表示从某个固定时间点开始的秒数的数值，例如
For example, you could convert the datetime into a numeric value representing a number of seconds from some fixed point in time, e.g.
TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)
然后您可以平均"这些值，以从固定时间点获得平均秒数.(注意:当心添加非常多的行，具有非常大的值，并超过限制(最大数值)，数字溢出问题.)
You could then "average" those values, to get an average number of seconds from a fixed point in time. (NOTE: beware of adding up an extremely large number of rows, with extremely large values, and exceeding the limit (maximum numeric value), numeric overflow issues.)
AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date))
要将其转换回日期时间，将该值作为秒数添加回固定时间点:
To convert that back to a datetime, add that value as a number of seconds back to a the fixed point in time:
'2015-01-01' + INTERVAL AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)) SECOND
(请注意，DATEIME 值是在 MySQL 会话的时区中评估的；因此在某些极端情况下，MySQL 会话中的 time_zone 变量的设置将对返回的值有一定的影响.)
(Note that the DATEIME values are evaluated in the timezone of the MySQL session; so there are edge cases where the setting of the time_zone variable in the MySQL session will have some influence on the value returned.)
MySQL 还提供了一个 UNIX_TIMESTAMP() 函数，它返回一个 unix 样式的整数值，即从纪元开始(UTC 时间 1970 年 1 月 1 日午夜)开始的秒数.您可以使用它来更简洁地完成相同的操作:
MySQL also provides a UNIX_TIMESTAMP() function which returns a unix-style integer value, number of seconds from the beginning of the era (midnight Jan. 1, 1970 UTC). You can use that to accomplish the same operation more concisely:
FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(t.my_date)))
请注意，这个最终表达式实际上是在做同样的事情...将日期时间值转换为自 '1970-01-01 00:00:00' UTC 以来的秒数，取其数值平均值，然后将该平均秒数添加回 '1970-01-01' UTC，最后将其转换回 DATETIME 值，以当前会话 time_zone 表示.
Note that this final expression is really doing the same thing... converting the datetime value into a number of seconds since '1970-01-01 00:00:00' UTC, taking a numeric average of that, and then adding that average number of seconds back to '1970-01-01' UTC, and finally converting that back to a DATETIME value, represented in the current session time_zone.
Q3:Django DateTimeField 是否未设置为处理平均?
答: 显然，Django 的作者对从数据库返回的 SQL 表达式 AVG(datetime) 的值感到满意.
A: Apparently, the authors of Django are satisfied with the value returned from the database for a SQL expression AVG(datetime).
这篇关于为什么 MYSQL DB 在对 Django models.DateTimeField 进行平均时返回损坏的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！