Django GROUP BY strftime date format

python django orm django-models django-aggregation
Andrew C picture Andrew C · Apr 6, 2009 · Viewed 9.8k times · Source

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

Andrew C picture Andrew C · Apr 6, 2009

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.

Related questions

How to store a dictionary on a Django Model?

I need to store some data in a Django model. These data are not equal to all instances of the model. At first I thought about subclassing the model, but I’m trying to keep the application flexible. If I …

Read More
Filter by property

Is it possible to filter a Django queryset by model property? i have a method in my model: @property def myproperty(self): [..] and now i want to filter by this property like: MyModel.objects.filter(myproperty=[..]) is this somehow possible?

Read More
How to TRUNCATE TABLE using Django's ORM?

To empty a database table, I use this SQL Query: TRUNCATE TABLE `books` How to I truncate a table using Django's models and ORM? I've tried this, but it doesn't work: Book.objects.truncate()

Read More