Opened 9 years ago

Closed 9 years ago

Last modified 8 years ago

#1423 closed enhancement (fixed)

[patch] MySQL get_data_trunc_sql using DATE_FORMAT() instead

Reported by: Geert Vanderkelen <geert@…> Owned by: adrian
Component: Database layer (models, ORM) Version: magic-removal
Severity: normal Keywords: mysql date_trunc get_date_trunc_sql
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (1)

mysql_date_trunc.diff (1.8 KB) - added by Geert Vanderkelen <geert@…> 9 years ago.
svn diff against the magic-removal branch

Download all attachments as: .zip

Change History (9)

comment:1 Changed 9 years ago by anonymous

  • Summary changed from MySQL get_data_trunc_sql using DATE_FORMAT() instead to [patch] MySQL get_data_trunc_sql using DATE_FORMAT() instead

comment:2 Changed 9 years ago by adrian

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

comment:3 Changed 9 years ago by adrian

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

comment:4 Changed 9 years ago by adrian

  • Resolution set to invalid
  • Status changed from new to closed

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).

comment:5 Changed 9 years ago by Geert Vanderkelen <geert@…>

  • Resolution invalid deleted
  • Status changed from closed to reopened

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),)

Changed 9 years ago by Geert Vanderkelen <geert@…>

svn diff against the magic-removal branch

comment:6 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • Triage Stage changed from Unreviewed to Design decision needed

Can someone please review the new patch.

comment:7 Changed 9 years ago by 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??)

comment:8 Changed 9 years ago by Gary Wilson <gary.wilson@…>

  • Resolution set to fixed
  • Status changed from reopened to closed

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

Note: See TracTickets for help on using tickets.
Back to Top