Django Queryset Postgres

Question

I am writing queryset that will return this type

date	total_shipping_fee
2021-04-16	5,000
2021-04-17	100,000

where

class Payments(models.Model):
    created = models.DateTimeField()
    ..... 

SELECT DATE(created) from payment_payment .... group by 'created' -> outputs a correct query

My question is how to query/ or cast

Payment.objects.filter(created_range=("2021-05-14", "2021-05-14")).values('created').annotate(total_shipping_fee=Sum('total_shipping_fee'))

so that I can have queryset in above raw sql. I think that is my problem to CAST DATE(created) in django queryset. Thanks

Answer 1

You can work with:

from django.db.models import F, Sum

Payment.objects.filter(
    created__date_range=('2021-05-14', '2021-05-14')
).values(create_date=F('created__date')).annotate(
    total_shipping_fee=Sum('total_shipping_fee')
).order_by('create_date')

here we thus first takes as values the truncated date of the DateTimeField, and then we use .order_by(…) [Django-doc] to enforce grouping by that date.

The output is a QuerySet of dictionaries, each with two items: { 'create_date': …, 'total_shipping_fee': … }.