CODEWITHSUNDEEP
djangodjango-queryset
PhoebeB
PhoebeB

Reputation: 8570

How to get month of a date in a django queryset

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}

Upvotes: 4

Views: 9844

Answers (2)

Bobort
Bobort

Reputation: 3218

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

Upvotes: 10

Evan Brumley
Evan Brumley

Reputation: 2468

You'll need to drop down into SQL to get this done, which sadly means the solution won't be database agnostic.

This works in Postgres:

# Grab the base queryset
items = Item.objects.all()

# Annotate
extra_query = "EXTRACT(MONTH FROM relatedtable.mydatefield)"
items = items.extra(select={'mydatefield_month': extra_query}

# We have to use a select_related so that 'relatedtable' is 
# available in the SQL statement.
items = items.select_related('relatedmodel')

print items[0].mydatefield_month

For MySQL you might try:

extra_query = "MONTH(relatedtable.mydatefield)"

Upvotes: 1

Related Questions