Django GROUP BY strftime date format

Question

I would like to do a SUM on rows in a database and group by date.

I am trying to run this SQL query using Django aggregates and annotations:

select strftime('%m/%d/%Y', time_stamp) as the_date, sum(numbers_data)
    from my_model
    group by the_date;

I tried the following:

data = My_Model.objects.values("strftime('%m/%d/%Y',
           time_stamp)").annotate(Sum("numbers_data")).order_by()

but it seems like you can only use column names in the values() function; it doesn't like the use of strftime().

How should I go about this?

Answer 1

This works for me:

select_data = {"d": """strftime('%%m/%%d/%%Y', time_stamp)"""}

data = My_Model.objects.extra(select=select_data).values('d').annotate(Sum("numbers_data")).order_by()

Took a bit to figure out I had to escape the % signs.

Answer 2

As of v1.8, you can use Func() expressions.

For example, if you happen to be targeting AWS Redshift's date and time functions:

from django.db.models import F, Func, Value

def TimezoneConvertedDateF(field_name, tz_name):
    tz_fn = Func(Value(tz_name), F(field_name), function='CONVERT_TIMEZONE')
    dt_fn = Func(tz_fn, function='TRUNC')
    return dt_fn

Then you can use it like this:

SomeDbModel.objects \
 .annotate(the_date=TimezoneConvertedDateF('some_timestamp_col_name',
                                           'America/New_York')) \
 .filter(the_date=...)

or like this:

SomeDbModel.objects \
 .annotate(the_date=TimezoneConvertedDateF('some_timestamp_col_name',
                                           'America/New_York')) \
 .values('the_date') \
 .annotate(...)