Annotate a request with an average date difference? (Django)

Question

I searched all over the place for the answer to this question, but could not find anything. Perhaps this is just a stupid question or a really difficult question. There he is:

Let's say my model is this (pseudo-django code):

Event
  type = ForeignKey(EventType)
  name = CharField
  date_start = DateField
  date_end = DateField

EventType
  name = CharField

What I want to know is the average duration time for each type of event. What I am doing now is calculating the average duration whenever a new event is created (save method) and stored in the average_duration column in EventType. The problem with this approach is that I cannot answer questions such as "what is the average time for events of type X during year Y." Therefore, instead of adding more columns to answer such questions, I would prefer it to be done in real time.

, ? , , , .

Answer 1

. django 1.8 :

from django.db.models import F, ExpressionWrapper, fields

duration = ExpressionWrapper(F('date_end') - F('date_start'), output_field=fields.DurationField())

events_with_duration = Event.objects.annotate(duration=duration)

, :

events_with_duration.filter(duration_gt=timedelta(days=10))

Answer 2

, :

event_duration = models.IntegerField()
...

def __init__(self, *args, **kwargs):
    super(Event, self).__init__(*args, **kwargs)
    self.update_event_duration()


def save(self, **kwargs):
    self.update_event_duration()
    super(Event, self).save(*args, **kwargs)

: http://code.google.com/p/django-cube/, (, )

>>> def my_avg(queryset):
...    return queryset.aggregate(Avg("event_duration"))["event_duration__avg"]

>>> c = Cube(["date_start__year", "type"], Event.objects.all(), my_avg)

:

>>> c.measure(date_start__year=1999, type=event_type2)
123.456

:

>>> c.measure_dict("date_start__year")
{1984: {'measure': 111.789}, 1985: {'measure': 234.666}, ...}

/ :

>>> c.measure_dict("date_start__year", "type")
{
    1984: {eventtype1: {'measure': 111.789}, eventtype2: {'measure': 234.666}, ...},
    1985: {eventtype1: {'measure': 122.79}, eventtype2: {'measure': 233.444}, ...},
    ...
}

Answer 3

, SQL- date_end - date_start, django, . , , , , , .

Answer 4

extra,

aggregate, :

, . event_type imho.