CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Reza Torkaman Ahmadi
Reza Torkaman Ahmadi

Reputation: 3058

Django: Group by date over a datetime field

I have for example a model like this:

class Transaction(models.Model):
    amount = models.FloatField()
    seller = models.ForeignKey(User, related_name='sells', on_delete=models.CASCADE)
    buyer = models.ForeignKey(User, related_name='purchased', on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)

I want to group by all transactions seperated by each date and find some of them. If the created_at is of type DateField I could simply find the count on each day by this query:

Transaction.objects..values('created_at').annotate(count=Count('created_at_date')

But this doesn't work DateTimeFeilds.

My question is how can i find total count of transactions for each date for this model type.

Upvotes: 6

Views: 4423

Answers (2)

typonaut
typonaut

Reputation: 321

I had a slightly different problem, in that I wanted to group by a partial date, but I think you should be able to adapt this to work with your requirements (Django 3.2+). Bonus: works with MySQL.

queryset = Viewlog.objects.all().values(
    year_month=Func(
        F('date'),
        Value('%Y-%m'),
        function='DATE_FORMAT',
        output_field=CharField())
    ).annotate(total=Count('year_month')).order_by('-year_month').distinct()

For your particular use:

Transaction.objects.all().values(year_month_day=Func(
        F('date'),
        Value('%Y-%m-%d'),
        function='DATE_FORMAT',
        output_field=CharField())
    ).annotate(total=Count('year_month_day')).order_by('-year_month_day').distinct()

For other databases you will need to change the function='DATE_FORMAT'

Adapted from: conversion of datetime Field to string in django queryset.values_list()

Upvotes: 0

coderDude
coderDude

Reputation: 904

The following will work for django 2.1 and above. 

 Transaction.objects.values('created_at__date').annotate(count=Count('id')).values('created_at__date', 'count').order_by('created_at__date')

You need to use __date after the name for lookup and need to put an order_by at the end.

Not sure about the previous django versions.

Upvotes: 16

Related Questions