Formatting dates for annotating count in Django + Python 3

Question

I'm currently trying to annotate and count some dates, based on the number of times they appear.

visits = Subs.objects.filter(camp=campdata, timestamp__lte=datetime.datetime.today(), timestamp__gt=datetime.datetime.today()-datetime.timedelta(days=30)).\
values('timestamp').annotate(count=Count('timestamp'))

If I print this in a for loop like,

for a in visits:
  print(a)

I would get back the following in Json.

{'timestamp': datetime.datetime(2018, 10, 5, 15, 16, 25, 130966, tzinfo=<UTC>), 'count': 1}
{'timestamp': datetime.datetime(2018, 10, 5, 15, 16, 45, 639464, tzinfo=<UTC>), 'count': 1}
{'timestamp': datetime.datetime(2018, 10, 6, 8, 43, 24, 721050, tzinfo=<UTC>), 'count': 1}
{'timestamp': datetime.datetime(2018, 10, 7, 4, 54, 59, tzinfo=<UTC>), 'count': 1}

This is kinda the right direction, however, it's counting to the second.. I just need to days, so that the event that happened on 2018, 10, 5 would be count: 2 for example.

Can anyone lead me into the right direction?

Additionally, whats the most "django" way of converting the dates into something more json / api friendly?

My ideal json return would be something like

{'timestamp': 2018-10-5, 'count': 2}

Thanks!

solarissmoke · Accepted Answer · 2018-10-07 15:00:08Z

11

You can use the TruncDate annotation to achieve this:

visits = Subs.objects.annotate(date=TruncDate('timestamp')).filter(
    camp=campdata, 
    date__lte=datetime.datetime.today(), 
    date__gt=datetime.datetime.today() - datetime.timedelta(days=30)
).values('date').annotate(count=Count('date'))

As for your question about serializing dates for JSON, Django provides the DjangoJSONEncoder to help with just that:

import json
from django.core.serializers.json import DjangoJSONEncoder

json.dumps(list(visits), cls=DjangoJSONEncoder)

edited Oct 7, 2018 at 15:00

answered Oct 7, 2018 at 11:41

solarissmoke

31.7k15 gold badges76 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronald Langeveld Over a year ago

awesome! Truncdate worked fantastic! I now get the queryset <QuerySet [{'date': datetime.date(2018, 10, 5), 'count': 2}, {'date': datetime.date(2018, 10, 6), 'count': 1}, {'date': datetime.date(2018, 10, 7), 'count': 1}]> I am however still having issues with the serializing AttributeError: 'dict' object has no attribute '_meta'

solarissmoke Over a year ago

Looks like serialize only works on model querysets - I've edited the answer to use json.dumps() instead, which should hopefully work.

Collectives™ on Stack Overflow

Formatting dates for annotating count in Django + Python 3

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related