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
1468	[patch] SQLite patch to use internal date conversion functions	mildly@…	Adrian Holovaty	"Some time ago I modified the SQLite driver to use SQLite's internal strftime(), instead of using type callbacks.  It's mostly straight-forward code, however it did require a small change to core/meta/fields.py to ensure it only generated YY-MM-DD for range searches.


{{{

Index: django/core/db/backends/sqlite3.py
===================================================================
--- django/core/db/backends/sqlite3.py  (revision 2491)
+++ django/core/db/backends/sqlite3.py  (working copy)
@@ -33,9 +33,6 @@
         from django.conf.settings import DATABASE_NAME, DEBUG
         if self.connection is None:
             self.connection = Database.connect(DATABASE_NAME, detect_types=Database.PARSE_DECLTYPES)
-            # register extract and date_trun functions
-            self.connection.create_function(""django_extract"", 2, _sqlite_extract)
-            self.connection.create_function(""django_date_trunc"", 2, _sqlite_date_trunc)
         cursor = self.connection.cursor(factory=SQLiteCursorWrapper)
         cursor.row_factory = utf8rowFactory
         if DEBUG:
@@ -88,19 +85,17 @@
     # lookup_type is 'year', 'month', 'day'
     # sqlite doesn't support extract, so we fake it with the user-defined
     # function _sqlite_extract that's registered in connect(), above.
-    return 'django_extract(""%s"", %s)' % (lookup_type.lower(), table_name)
+    strfvals = {
+        'year': '%%Y',
+        'month': '%%m',
+        'day': '%%d',
+    }
+    return 'strftime( %s, ""%s"" ) ' % ( table_name, strfvals[lookup_type.lower()] )

-def _sqlite_extract(lookup_type, dt):
-    try:
-        dt = typecasts.typecast_timestamp(dt)
-    except (ValueError, TypeError):
-        return None
-    return str(getattr(dt, lookup_type))
-
 def get_date_trunc_sql(lookup_type, field_name):
     # lookup_type is 'year', 'month', 'day'
     # sqlite doesn't support DATE_TRUNC, so we fake it as above.
-    return 'django_date_trunc(""%s"", %s)' % (lookup_type.lower(), field_name)
+    return 'strftime( ""%%%%Y-%%%%m-%%%%d 00:00:00"", %s, ""start of %s"" )' % (field_name, lookup_type.lower())

 def get_limit_offset_sql(limit, offset=None):
     sql = ""LIMIT %s"" % limit
@@ -111,18 +106,6 @@
 def get_random_function_sql():
     return ""RANDOM()""

-def _sqlite_date_trunc(lookup_type, dt):
-    try:
-        dt = typecasts.typecast_timestamp(dt)
-    except (ValueError, TypeError):
-        return None
-    if lookup_type == 'year':
-        return ""%i-01-01 00:00:00"" % dt.year
-    elif lookup_type == 'month':
-        return ""%i-%02i-01 00:00:00"" % (dt.year, dt.month)
-    elif lookup_type == 'day':
-        return ""%i-%02i-%02i 00:00:00"" % (dt.year, dt.month, dt.day)
-
 def get_table_list(cursor):
     cursor.execute(""SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"")
     return [row[0] for row in cursor.fetchall()]
Index: django/core/meta/fields.py
===================================================================
--- django/core/meta/fields.py  (revision 2491)
+++ django/core/meta/fields.py  (working copy)
@@ -389,7 +389,7 @@

     def get_db_prep_lookup(self, lookup_type, value):
         if lookup_type == 'range':                                                                                                  
}}}
"	defect	closed	Database layer (models, ORM)		normal	wontfix	sqlite		Design decision needed	1	0	1	0	0	0