id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
1423	[patch] MySQL get_data_trunc_sql using DATE_FORMAT() instead	Geert Vanderkelen <geert@…>	Adrian Holovaty	"Hi!

My first hour Django, but here is my first small contribution. Hope this helps!

This is get_date_trunc_sql using DATE_FORMAT() instead of the INTERVAL. Since we don't have DATE_TRUNC() (yet??) in MySQL, I think this might just do the trick.
Btw, I used the same lookup_type as for PostgreSQL's DATE_TRUNC() function.

{{{
def get_date_trunc_sql(lookup_type, field_name):

    # Fieldnames used like PostgreSQL's date_trunc() function
    fields = [ 'year','month','day','hour','minute','second']
    
    # Default format: %Y-%m-%d %H-%i-%s, and the corresponding 'null' values
    format = [ '%Y-','%m','-%d',' %H:','%i',':%s']
    format_def = [ '0000-','01','-01',' 00:','00',':00']
    
    sql = ''
    
    try:
        i = fields.index(lookup_type) + 1
    except ValueError:
        # Could not find the type, so lets just do nothing!
        sql = field_name
    else:
        str = ''
        
        # Loop over the format
        for f in format[:i]:
            str = str + f
            
        # Get the part which we need to truncate
        for f in format_def[i:]:
            str = str + f
            
        # Use DATE_FORMAT() with the field and the format
        sql = ""DATE_FORMAT(%s,'%s')"" % (field_name, str)

    return sql

sql = get_date_trunc_sql('day', ""'2005-02-28 19:23:45'"")
print ""SELECT "" + sql;
}}}


Thanks!

Geert

"	enhancement	closed	Database layer (models, ORM)	magic-removal	normal	fixed	mysql date_trunc get_date_trunc_sql		Accepted	1	0	0	0	0	0