英文:
Django: TruncDate datetime retunrs always datetime
问题
我正在使用Django 3.2.12和mysql 8.0.27 for Win64 on x86_64 (MySQL Community Server - GPL)，并且有以下这些模型：
class Anomalie(models.Model):
dateajout = models.DateTimeField(auto_now_add=True, null=True)
我想按天获取所有的Anomalie，所以我使用了以下代码：
items = Anomalie.objects.annotate(date=TruncDate('dateajout')).values('dateajout').annotate(count=Count('id')).values('dateajout', 'count')
但是当我打印items时，我得到了以下结果：
<QuerySet [{'dateajout': datetime.datetime(2023, 7, 4, 1, 58, 15, 509978, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 10, 12, 56, 9, 682396, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 11, 12, 23, 54, 838830, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 12, 13, 5, 38, 557618, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 3, 57, 31, 69749, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 14, 15, 38, 704047, tzinfo=<UTC>), 'count': 1}]>
正如你所看到的，即使在同一天（2023年8月6日）创建了2个异常，我始终得到了"count: 1"。我认为这是因为TruncDate返回的是datetime而不是date。
我在许多论坛上搜索了解决方法，例如https://forum.djangoproject.com/t/problem-with-count-and-truncdate/10122和https://stackoverflow.com/questions/52687170/formatting-dates-for-annotating-count-in-django-python-3，但我不知道我做错了什么... 请问有什么建议吗？
英文:
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=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 10, 12, 56, 9, 682396, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 11, 12, 23, 54, 838830, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 7, 12, 13, 5, 38, 557618, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 3, 57, 31, 69749, tzinfo=<UTC>), 'count': 1}, {'dateajout': datetime.datetime(2023, 8, 6, 14, 15, 38, 704047, tzinfo=<UTC>), '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 https://stackoverflow.com/questions/52687170/formatting-dates-for-annotating-count-in-django-python-3 but I don't know what I'm doing wrong... Any idea please?
答案1
得分: 1
你不应该使用dateajout，它仍然是datetime类型，你应该使用你注释的date字段：
items = (
Anomalie.objects.annotate(date=TruncDate('dateajout'))
.values('date')
.annotate(count=Count('id'))
.values('date', 'count')
)
你可以简化为：
items = Anomalie.objects.values(date=TruncDate('dateajout')).annotate(
count=Count('id')
).order_by('date')
英文:
You should not use dateajout, that is still the datetime, you use date, the thing you annotated:
<pre><code>items = (
Anomalie.objects.annotate(<b>date=TruncDate('dateajout')</b>)
.values(<b>'date'</b>)
.annotate(count=Count('id'))
.values(<b>'date'</b>, 'count')
)</code></pre>
You can simplify this to:
<pre><code>items = Anomalie.objects.values(<b>date=TruncDate('dateajout')</b>).annotate(
count=Count('id')
)<b>.order_by('date')</b></code></pre>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论