15

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?

priestc
  • 33,060
  • 24
  • 83
  • 117
Andrew C
  • 1,036
  • 2
  • 9
  • 19

5 Answers5

14

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
  • 1,036
  • 2
  • 9
  • 19
  • I got something like: `Out[15]: ) failed: django.db.utils.OperationalError: (1305, 'FUNCTION xxxxxx.strftime does not exist')>` any ideas why? – user2002692 May 13 '17 at 19:23
  • I am getting the same error. Did you find out why it couldn't recognise strftime? – Sid Jun 08 '17 at 11:29
5

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(...)
HostedMetrics.com
  • 3,525
  • 3
  • 26
  • 31
1

I'm not sure about strftime, my solution below is using sql postgres trunc...

select_data = {"date": "date_trunc('day', creationtime)"}       
ttl = ReportWebclick.objects.using('cms')\
                    .extra(select=select_data)\
                    .filter(**filters)\
                    .values('date', 'tone_name', 'singer', 'parthner', 'price', 'period')\
                    .annotate(loadcount=Sum('loadcount'), buycount=Sum('buycount'), cancelcount=Sum('cancelcount'))\
                    .order_by('date', 'parthner')

-- equal to sql query execution:

select date_trunc('month', creationtime) as date, tone_name, sum(loadcount), sum(buycount), sum(cancelcount)
from webclickstat
group by tone_name, date;
mishaikon
  • 461
  • 5
  • 12
1

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
  • 34,554
  • 11
  • 80
  • 120
  • 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 Apr 06 '09 at 17:47
  • I mean, just use whatever facility exisyts in Django to make a straight sql select. – tpdi Apr 06 '09 at 18:04
0

my solution like this when my db is mysql:

select_data = {"date":"""FROM_UNIXTIME( action_time,'%%Y-%%m-%%d')"""} 
qs = ViewLogs.objects.filter().extra(select=select_data).values('mall_id', 'date').annotate(pv=Count('id'), uv=Count('visitor_id', distinct=True))

to use which function, you can read mysql datetime processor docs like DATE_FORMAT,FROM_UNIXTIME...

GuRu
  • 1,880
  • 3
  • 23
  • 32
Steven
  • 1