How to get month of a date in a django queryset

Question

I want to include a month number in a queryset where the date is in a related model. This is what I tried:

 OrderItem.objects.all().annotate(order_month=Sum('order__payment_date__month'))[0].__dict__

 Join on field 'payment_date' not permitted. Did you misspell 'month' for the lookup type?

and then I tried

 OrderItem.objects.all().extra(select={'order_month': "order__payment_date__month"})

 (1054, "Unknown column 'order__payment_date__month' in 'field list'")

 OrderItem.objects.all().extra(select={'order_month': "order.payment_date"}).select_related('Order')

 (1054, "Unknown column 'order.payment_date' in 'field list'")

But this works so no problem with order.payment_date

 OrderItem.objects.all().values('id','order__payment_date').select_related('Order')

I need it in the querset result as I'm using the queryset in Geraldo. Anyone know how I can get this?

THE ANSWER was that in the extra section you need to specify what you want so the MySQL understands it. In my case adding the app in front of the model name. In this case web_order.payment_date. This worked:

OrderItem.objects.all().extra(select={'order_month': "MONTH(web_order.payment_date)"}).select_related('order')[0].__dict__

{'product_id': None, 'order_id': 1L, 'price': Decimal("1.00"),  'order_month': 7L, 'id': 1L}

Answer 1

In Django 1.10+, you can use the ExtractMonth function.

from django.db.models.functions import ExtractMonth
OrderItem.objects.all().annotate(order_month=ExtractMonth('order__payment_date'))