views:

435

answers:

1

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!

+2  A: 

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 ;
tpdi
Thanks! I haven't quite got that to work yet. Where can I read more about custom MySQL functions? I looked here, but didn't find much: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html
Andrew C
Got it working. In your example, the params for the custom function need to be ordered like: param_name data_type.So it should say: create function strftime(d datetime, format varchar(255))Thanks for the guidance!
Andrew C
Yeah, I whip back and forth between C/C++/Java's type identifier and SQL's identifier type. I guess I got whiplash. Or lashwhip.
tpdi
Oh, if it worked, I'd appreciate an upmod or accepted answer, or both. Thanks.
tpdi