views:

1844

answers:

2

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?

+1  A: 

Any reason not to just do this in the database, by running the following query against the database:

select date, sum(numbers_data) 
from my_model 
group by date;

If your answer is, the date is a datetime with non-zero hours, minutes, seconds, or milliseconds, my answer is to use a date function to truncate the datetime, but I can't tell you exactly what that is without knowing what RBDMS you're using.

tpdi
Right now I am just using sqlite3 for proof of concept. The "date" is indeed a datetime field. I was using strftime() to truncate the datetime field to a date. Is there something better for sqlite3? What do you mean by "just do this in the database"? Thanks!
Andrew C
I mean, just use whatever facility exisyts in Django to make a straight sql select.
tpdi
+4  A: 

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.

Andrew C