I'm using Django 3.2.12 and mysql 8.0.27 for Win64 on x86_64 (MySQL Community Server - GPL) and have this models
class Anomalie(models.Model):
dateajout = models.DateTimeField(auto_now_add=True, null=True)
I am trying to get all Anomalie per day so I'm using this:
items = Anomalie.objects.annotate(date=TruncDate('dateajout')).values('dateajout').annotate(count=Count('id')).values('dateajout', 'count')
but I get this when I print items:
<QuerySet [{'dateajout': datetime.datetime(2023, 7, 4, 1, 58, 15, 509978, tzinfo=), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 10, 12, 56, 9, 682396, tzinfo=), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 11, 12, 23, 54, 838830, tzinfo=), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 12, 13, 5, 38, 557618, tzinfo=), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 3, 57, 31, 69749, tzinfo=), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 14, 15, 38, 704047, tzinfo=), 'count': 1}]>
As you can see, I always get "'count': 1", even if 2 anomalies were created on same day (2023, 8, 6)... I think it's because TruncDate returns a datetime instead of a date...
I searched on many forums like https://forum.djangoproject.com/t/problem-with-count-and-truncdate/10122 or Formatting dates for annotating count in Django + Python 3 but I don't know what I'm doing wrong... Any idea please?
You should not use dateajout
, that is still the datetime
, you use date
, the thing you annotated:
items = (
Anomalie.objects.annotate(date=TruncDate('dateajout'))
.values('date')
.annotate(count=Count('id'))
.values('date', 'count')
)
You can simplify this to:
items = Anomalie.objects.values(date=TruncDate('dateajout')).annotate(
count=Count('id')
).order_by('date')