Aggregation and annotation by time period and intervals (by month, week, day, hour, etc)

[Austin Pua]

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 ​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.

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