How to do a group by/count(*) pr year, month and day in django based on a datetime database field?

Question

I have a table describing files with a datetime field. I want to somehow create a report that gives me the number of files grouped by each year, number of files grouped by each year and month and number of files grouped by each year, month and day. I just want records where count(*) > 0. Preferably using the ORM in django or if that`s not possible, using some SQL that runs on both PostgreSQL and SQLite.

The number of records in this database can be huge so my attempts to do this in code, not in SQL ( or indirectly in SQL thru ORM ) don't work and if I get it to work I don`t think it will scale at all.

Grateful for any hints or solutions.

Answer 1

Normally I work on Oracle but a quick google search showed that this should also work for Postgres. For the minutes you could do like this

select to_char(yourtimestamp,'yyyymmdd hh24:mi'), count(*)
from yourtable
group by to_char(yourtimestamp,'yyyymmdd hh24:mi') 
order by to_char(yourtimestamp,'yyyymmdd hh24:mi') DESC;

That works then all the way down to years:

select to_char(yourtimestamp,'yyyy'), count(*)
from yourtable
group by to_char(yourtimestamp,'yyyy') 
order by to_char(yourtimestamp,'yyyy') DESC;

You are only getting the years where you got something. I think that is what you wanted.

Edit: You need to build an index on "yourtimestamp" otherwise the performance is ugly if you do have a lot of rows.

Answer 2

My mistake - the date() function only works for MySql:

Maybe try this (SQLite):

tbl = MyTable.objects.filter()                                       
tbl = tbl.extra(select={'count':'count(strftime('%Y-%m-%d', timestamp))', 'my_date':'strftime('%Y-%m-%d', timestamp))'}
tbl = tbl.values('count', 'my_date')
tbl.query.group_by = ['strftime('%Y-%m-%d', timestamp)']

For day and month, you could replace '%Y-%m-%d' with variations of the date format strings.

This was for MySQL (just in case someone needs it)

tbl = MyTable.objects.filter()                                       
tbl = tbl.extra(select={'count':'count(date(timestamp))', 'my_date':'date(timestamp)'}
tbl = tbl.values('count', 'my_date')
tbl.query.group_by = ['date(timestamp)']

That works for year.