id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25339	Aggregation and annotation by time period and intervals (by month, week, day, hour, etc)	Austin Pua	nobody	"I often found that aggregation of data by dynamically generating time periods given a desired time interval is a very sought after feature. I personally created the snippet below to accommodate my needs, but as per the note [https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra here], the extra() method will be deprecated soon. I think it will be a waste to deprecate this API, since there is simply a lot of DBMS-specific SQL functions available, but if there is any way we can do this via Django ORM, then I am still up for it. The snippet below shows a custom QuerySet method I often use.

{{{#!python
def get_metrics(self, frequency):
    select_fields = OrderedDict()
    if frequency == 'week':
        select_fields['time_period'] = ""date_trunc(%s, initial_timestamp::TIMESTAMP WITH TIME ZONE AT TIME ZONE %s + '1 day'::interval) - '1 day'::interval""
    else:
	select_fields['time_period'] = ""date_trunc(%s, initial_timestamp::TIMESTAMP WITH TIME ZONE AT TIME ZONE %s)""
    select_params = (frequency, settings.TIME_ZONE,)
    queryset = self.extra(
        select=select_fields,
        select_params=select_params
    ).values('time_period', ....)
    queryset = queryset.annotate(
        # Add annotations
    )
    return queryset
}}}
"	New feature	new	Database layer (models, ORM)	1.8	Normal		QuerySet.extra		Unreviewed	0	0	0	0	0	0