Selecting date formats for aggregate calculations from database with Django

Question

I would like to do aggregate calculations based on month for a datetime field.

I am currently using the extra() function to format the date like:

...extra(select="strftime('column', '%m/%Y') as t").values('t').annotate(SUM(foo))

and it works great for sqlite3.

In sqlite3 I can use strftime(), but that doesn't work with MySQL. In MySQL I can use date_format(), but that doesn't work with sqlite3.

How can I rewrite this to work for both database types?

Most of the developers simply use sqlite3 on their dev machines, and MySQL is used on the prod server.

Thanks!

Answer 1

Run the following in your MySQL database, to create a forwarding function.

delimiter //

create function strftime ( d date, format varchar(255) )   
   RETURNS varchar(64)
   LANGUAGE SQL
   DETERMINISTIC
   COMMENT 'synonym for date_format'
   return date_format(d, format) ;
//
delimiter ;