python - Django query group by month and year -


i have django model this:

class event(models.model):     user = models.charfield(max_length=255)     pay_time = models.datetimefield(default=timezone.now) 

i need calculate average number of records per month have group of users. have user names in list , want group month , year use in average calculation. query this:

from django.db import models django.db.models import func, f, count class month(func):     function = 'extract'     template = '%(function)s(month %(expressions)s)'     output_field = models.integerfield()  class year(func):     function = 'extract'     template = '%(function)s(year %(expressions)s)'     output_field = models.integerfield()  #ru list of usernames want average per month trx = event.objects.filter(user__in=ru).annotate(m=month('pay_time'), y=year('pay_time')).values('m', 'y').annotate(c=count('id')) 

when run query results thought wanted. that's sample of got:

<queryset [{'y': 2016, 'c': 61098, 'm': 4}, {'y': 2016, 'c': 104632, 'm': 5}]> 

i wanted make sure ran query:

trx2 = event.objects.filter(user__in=ru, pay_time__year=2016, pay_time__month=4)  

and got result 60990 records.
means results got first query incorrect. know can use second query in loop want slow compared running 1 query using group by.

thanks,

edit:
here output of print trx.query:

select      extract(year `events_event`.`pay_time`) `y`,       extract(month `events_event`.`pay_time`) `m`,     count(`events_event`.`id`) `c` `events_event`    `events_event`.`user` in ('user1', 'user2') group    extract(year `events_event`.`pay_time`),   extract(month `events_event`.`pay_time`)   order null   

and output of print trx2.query:

select     `events_event`.`id`,     `events_event`.`user`,     `events_event`.`pay_time`, `events_event`     (`events_event`.`user` in ('user1', 'user2')  , `events_event`.`pay_time` between 2015-12-31 22:00:00 , 2016-12-31 21:59:59.999999   , extract(month convert_tz(`events_event`.`pay_time`, 'utc', africa/cairo)) = 4)   

i think solution here, in convert_tz

extract(month convert_tz(`events_event`.`pay_time`, 'utc', africa/cairo)) 

you can try

class month(func):     function = 'extract'     template = "%(function)s(month convert_tz(%(expressions)s, 'utc', 'africa/cairo'))"     output_field = models.integerfield() 

Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -