Choosing a date format for aggregate calculations from a database using Django

Question

I would like to perform aggregate calculations based on the month for the datetime field.

I am currently using the extra () function to format the date, for example:

...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 this does not work with MySQL. In MySQL, I can use date_format (), but this does not work with sqlite3.

How can I rewrite this to work for both types of databases?

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

Thanks!

Answer 1

MySQL, .

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 ;