1

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?

al78310
  • 83
  • 9

1 Answers1

1

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')
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555