¿Por qué MYSQL DB devuelve un valor dañado al promediar un modelo Django.DateTimeField?

Estoy ejecutando una aplicación Django sobre una base de datos MySQL (en realidad, MariaDB).

Mi modelo Django se parece a esto:

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'), ) ) 

Cuando ejecuto get_stats() , obtengo la siguiente línea de registro:

 [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=), 'max': datetime.datetime(2009, 2, 13, 5, 20, 6, tzinfo=)}] 

El problema que tengo con esto es que el promedio del campo my_date devuelto por la base de datos es: 20080507582679.5 . Mira cuidadosamente ese número. Es un formato de fecha no válido.

¿Por qué la base de datos no devuelve un valor válido para el promedio de estas dos fechas? ¿Cómo obtengo el promedio real de este campo si falla la forma descrita? ¿Django DateTimeField no está configurado para manejar el promedio?

P1: ¿Por qué la base de datos no devuelve un valor válido para el promedio de estas dos fechas?

R: El valor devuelto es esperado, está bien definido el comportamiento de MySQL.

MySQL convierte automáticamente un valor de fecha u hora en un número si el valor se utiliza en un contexto numérico y viceversa.

MySQL Reference Manual: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html


En MySQL, la función agregada de AVG opera con valores numéricos .

En MySQL, una expresión DATE o DATETIME se puede evaluar en un contexto numérico .

Como una simple demostración, realizar una operación de sum numérica en un DATETIME convierte implícitamente el valor de fecha y hora en un número. Esta consulta:

  SELECT NOW(), NOW()+0 

devuelve un resultado como:

  NOW() NOW()+0 ------------------- ----------------------- 2015-06-23 17:57:48 20150623175748.000000 

Tenga en cuenta que el valor devuelto para la expresión NOW()+0 no es un DATETIME , es un número .

Cuando especifica una función SUM() o AVG() en una expresión DATETIME , eso equivale a convertir DATETIME en un número y luego sumr o promediar el número.

Es decir, el retorno de esta expresión AVG(mydatetimecol) es equivalente al retorno de esta expresión: AVG(mydatetimecol+0)

Lo que se está “promediando” es un valor numérico. Y usted ha observado que el valor devuelto no es una fecha y hora válida; e incluso en los casos en que parece ser una fecha y hora válida, es probable que no sea un valor que consideraría un verdadero “promedio”.


P2: ¿Cómo obtengo el promedio real de este campo si falla la forma descrita?

A2: Una forma de hacerlo es convertir el datetime en un valor numérico que pueda promediarse “con precisión”, y luego convertirlo nuevamente en un datetime.

Por ejemplo, podría convertir la fecha y hora en un valor numérico que represente una cantidad de segundos desde un punto fijo en el tiempo, por ejemplo,

  TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date) 

Luego podría “promediar” esos valores para obtener un promedio de segundos desde un punto fijo en el tiempo. (NOTA: tenga cuidado de sumr un número extremadamente grande de filas, con valores extremadamente grandes y que exceda el límite (valor numérico máximo), problemas de desbordamiento numérico).

  AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)) 

Para convertirlo nuevamente en una fecha y hora, agregue ese valor como un número de segundos al punto fijo en el tiempo:

  '2015-01-01' + INTERVAL AVG(TIMESTAMPDIFF(SECOND,'2015-01-01',t.my_date)) SECOND 

(Tenga en cuenta que los valores DATEIME se evalúan en la zona horaria de la sesión de MySQL; por lo tanto, hay casos de borde en los que la configuración de la variable time_zone en la sesión de MySQL tendrá cierta influencia en el valor devuelto).

MySQL también proporciona una función UNIX_TIMESTAMP() que devuelve un valor entero de estilo Unix, número de segundos desde el comienzo de la era (medianoche del 1 de enero de 1970 UTC). Puedes usar eso para realizar la misma operación de manera más concisa:

  FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(t.my_date))) 

Tenga en cuenta que esta expresión final realmente está haciendo lo mismo … convertir el valor de fecha y hora en un número de segundos desde ‘1970-01-01 00:00:00’ UTC, tomando un promedio numérico de eso, y luego agregando ese promedio número de segundos de vuelta a ‘1970-01-01’ UTC, y finalmente convirtiéndolo de nuevo en un valor DATETIME , representado en la sesión actual time_zone .


P3: ¿Django DateTimeField no está configurado para manejar el promedio?

R: Aparentemente, los autores de Django están satisfechos con el valor devuelto por la base de datos para una expresión de SQL AVG(datetime) .

Plan A: use un campo TIMESTAMP en lugar de un campo DATETIME

Plan B: Convierta DATETIME a TIMESTAMP durante el cálculo:

 FROM_UNIXTIME(ROUND(AVG(UNIX_TIMESTAMP(`my_date`)))) 

(Lo siento, no sé la syntax de Django que se necesita).

Cuando usas values() , Django no convertirá el valor que obtuvo del conector python de la base de datos. Depende del conector para determinar cómo se devuelve el valor.

En este caso, parece que el conector MySQL devuelve una representación de cadena con los separadores eliminados. Puede intentar usar datetime.strptime() con un format coincidente para analizarlo en un objeto datetime .