In django, how to annotate(group by) date from a datetime field with MYSQL-CodePudding

Defined as

class Data(models.Model):
    created_at = models.DateTimeField(default=timezone.now)
    number = models.IntegerField(default=1)

And the requirement is to get sum group by date from created_at and I was using.

qs = Data.objects.values('created_at__date').annotate(sum=Sum('number'))

The above query works fine with sqlite3, but it failed with MySql. In MySql, it returns single result <QuerySet [{'created_at__date': None, 'sum': 100}]>

Excepted:

<QuerySet [
  {'created_at__date': datetime.date(2021, 7, 12), 'sum': 50}, 
  {'created_at__date': datetime.date(2021, 7, 13), 'sum': 50}
]>

CodePudding user response：

Found a solution, Try this way:

qs = Data.objects.annotate(sum=Sum('number')).annotate(created_at__date=TruncDate('created_at')).values("created_at__date","sum")