1

I was looking around to see how to group a django queryset by months. I've finally found this very helpful piece of code:

truncate_date = connection.ops.date_trunc_sql('day','timestamp')
qs = qs.extra({'date':truncate_date})
return qs.values('date').annotate(Sum('amount')).order_by('date')

from Django: Group by date (day, month, year)

It worked fine, however the date returned is a string and not a proper datetime.date and I can't use {{date|date:format}} in the template. It seems the the original sender (Oli) had the same problem. Although the guy who is answering says it works with postgres I didn't have any success with SQLite.

Is it possible to convert from the string to datetime in the query or template. Or do I have to loop over all entries?

Sorry for posting in a separated trend, but I don't have enough privilegies to just comment in the original one.

Community
  • 1
  • 1
Fernando Ferreira
  • 798
  • 1
  • 11
  • 26

1 Answers1

7

You can try use the regroup template tag to group by months at the template level. Make sure you context-data is sorted:

{% regroup queryset by date_field|date:"M, Y" as objects_by_month %}
{% for group in objects_by_month %}
    {{ group.grouper }}
    {% for object in group.list %}
        {{ object }}
    {% endfor %}
{% endfor %}
keithhackbarth
  • 9,317
  • 5
  • 28
  • 33
Timmy O'Mahony
  • 53,000
  • 18
  • 155
  • 177
  • But then the "amount" will not sum all the entries for a month as I need (the other post was about it). my current query looks like this: `truncate_date = connection.ops.date_trunc_sql('month', 'data_insercao')` `fichas_report = Ficha.objects.extra(select={'month': truncate_date}).values('name', 'month'). annotate(number_entries=Count('pk')).order_by('-month')` It gives me a list of names and entries by month. – Fernando Ferreira Mar 30 '12 at 17:23