[patch] MySQL get_data_trunc_sql using DATE_FORMAT() instead

[Geert Vanderkelen <geert@…>]

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

[Adrian Holovaty]

(In [2449]) Added model unit tests for get_DATEFIELD_list(). Refs #1423

[Adrian Holovaty]

(In [2450]) Added note to mysql backend about DATE_FORMAT not working. Refs #1423

[Adrian Holovaty]

Thanks for this patch, but it won't do exactly what we need. It formats the date properly, but the date formatting returns a string rather than a date object. And Django needs this function to return a date/datetime object so that the typecasting will work correctly (i.e., so the result will be converted to a Python datetime object rather than a string).

[Geert Vanderkelen <geert@…>]

Understood.. and this is closed, but here is the solution then.
We just need a CAST( .. AS DATETIME) around DATE_FORMAT()

    sql = "CAST(DATE_FORMAT(%s,'%s') AS DATETIME)" % (field_name, str)

This returns then a datetime object, like:

SELECT CAST(DATE_FORMAT('2005-02-28 19:23:45','%Y-%m-%d 00:00:00') AS DATETIME)
(datetime.datetime(2005, 2, 28, 0, 0),)

[Geert Vanderkelen <geert@…>]

svn diff against the magic-removal branch

[Gary Wilson <gary.wilson@…>]

Can someone please review the new patch.

[geert@…]

Not sure what's still needed here, but my patch made it (though a bit altered) in the SVN or I'm using a bad branch..

(why are email addresses shown here??)

[Gary Wilson <gary.wilson@…>]

Ok, thanks. The last bit of code for this ticket happened in [2735].