Django annotation per day time range

Question

I am using the following query to get a sum per day:

orders = OrderM.objects.filter(user=request.user) \
         .annotate(day=TruncDay('datetime_added')) \
         .values('day') \                                   
         .annotate(sum=Sum(F('orderd__good_count'))) \
         .order_by('-day')

as expected each day changes at 00:00 midnight! I want to change this behaviour so that the sum to be generated on periods from 06:00 to 06:00 of each day. I think that the use of datetime__range = .. will do, but I'm not experienced in django queries.

Thanks...

Answer 1

This seems to do the work:

from django.db.models import DateTimeField, ExpressionWrapper, F
from django.utils.timezone import timedelta

expression = F('datetime_added') - timedelta(hours=6)
wrapped_expression = ExpressionWrapper(expression, output_field=DateTimeField())

orders = OrderM.objects.filter(user=request.user) \
               .annotate(day=TruncDay(wrapped_expression)) \
               .values('day') \
               .annotate(sum=Sum(F('orderd__good_count'))) \
               .order_by('-day')

Time portion of TruncDay('datetime_added') is 00:00 midnight by definition. So we have to subtract 6 hours from each datetime field before we truncate. The following will actually change the day portion, after 6 in the morning!

`TruncDay( F('datetime_added') - timedelta(hours=6) )` 

The use of ExpressionWrapper is essential, since I get

Expression contains mixed types. You must set output_field

, otherwise (and I don't know why!).