extract() is now dialect-sensitive and supports SQLite and others.

6 files changed, 75 insertions, 1 deletions

diff --git a/lib/sqlalchemy/databases/access.py b/lib/sqlalchemy/databases/access.py
index 67af4a7a4..56c28b8cc 100644
--- a/lib/sqlalchemy/databases/access.py
+++ b/lib/sqlalchemy/databases/access.py
@@ -328,6 +328,20 @@ class AccessDialect(default.DefaultDialect):
 
 
 class AccessCompiler(compiler.DefaultCompiler):
+    extract_map = compiler.DefaultCompiler.extract_map.copy()
+    extract_map.update ({
+            'month': 'm',
+            'day': 'd',
+            'year': 'yyyy',
+            'second': 's',
+            'hour': 'h',
+            'doy': 'y',
+            'minute': 'n',
+            'quarter': 'q',
+            'dow': 'w',
+            'week': 'ww'
+    })
+
     def visit_select_precolumns(self, select):
         """Access puts TOP, it's version of LIMIT here """
         s = select.distinct and "DISTINCT " or ""
@@ -375,6 +389,10 @@ class AccessCompiler(compiler.DefaultCompiler):
         return (self.process(join.left, asfrom=True) + (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN ") + \
             self.process(join.right, asfrom=True) + " ON " + self.process(join.onclause))
 
+    def visit_extract(self, extract):
+        field = self.extract_map.get(extract.field, extract.field)
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+
 
 class AccessSchemaGenerator(compiler.SchemaGenerator):
     def get_column_specification(self, column, **kwargs):
@@ -422,4 +440,4 @@ dialect.schemagenerator = AccessSchemaGenerator
 dialect.schemadropper = AccessSchemaDropper
 dialect.preparer = AccessIdentifierPreparer
 dialect.defaultrunner = AccessDefaultRunner
-dialect.execution_ctx_cls = AccessExecutionContext
\ No newline at end of file
+dialect.execution_ctx_cls = AccessExecutionContext
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 63ec8da15..03cf73eee 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -1515,6 +1515,14 @@ class MSSQLCompiler(compiler.DefaultCompiler):
         }
     )
 
+    extract_map = compiler.DefaultCompiler.extract_map.copy()
+    extract_map.update ({
+        'doy': 'dayofyear',
+        'dow': 'weekday',
+        'milliseconds': 'millisecond',
+        'microseconds': 'microsecond'
+    })
+
     def __init__(self, *args, **kwargs):
         super(MSSQLCompiler, self).__init__(*args, **kwargs)
         self.tablealiases = {}
@@ -1586,6 +1594,10 @@ class MSSQLCompiler(compiler.DefaultCompiler):
         kwargs['mssql_aliased'] = True
         return super(MSSQLCompiler, self).visit_alias(alias, **kwargs)
 
+    def visit_extract(self, extract):
+        field = self.extract_map.get(extract.field, extract.field)
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+
     def visit_savepoint(self, savepoint_stmt):
         util.warn("Savepoint support in mssql is experimental and may lead to data loss.")
         return "SAVE TRANSACTION %s" % self.preparer.format_savepoint(savepoint_stmt)
diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py
index 3d71bb723..c2b233a6e 100644
--- a/lib/sqlalchemy/databases/mysql.py
+++ b/lib/sqlalchemy/databases/mysql.py
@@ -1914,6 +1914,10 @@ class MySQLCompiler(compiler.DefaultCompiler):
         "utc_timestamp":"UTC_TIMESTAMP"
         })
 
+    extract_map = compiler.DefaultCompiler.extract_map.copy()
+    extract_map.update ({
+        'milliseconds': 'millisecond',
+    })
 
     def visit_typeclause(self, typeclause):
         type_ = typeclause.type.dialect_impl(self.dialect)
diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py
index 038a9e8df..068afaf3d 100644
--- a/lib/sqlalchemy/databases/postgres.py
+++ b/lib/sqlalchemy/databases/postgres.py
@@ -792,6 +792,12 @@ class PGCompiler(compiler.DefaultCompiler):
         else:
             return text
 
+    def visit_extract(self, extract, **kwargs):
+        field = self.extract_map.get(extract.field, extract.field)
+        return "EXTRACT(%s FROM %s::timestamp)" % (
+            field, self.process(extract.expr))
+
+
 class PGSchemaGenerator(compiler.SchemaGenerator):
     def get_column_specification(self, column, **kwargs):
         colspec = self.preparer.format_column(column)
diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py
index 77eb30ff8..b77a315b8 100644
--- a/lib/sqlalchemy/databases/sqlite.py
+++ b/lib/sqlalchemy/databases/sqlite.py
@@ -557,12 +557,34 @@ class SQLiteCompiler(compiler.DefaultCompiler):
         }
     )
 
+    extract_map = compiler.DefaultCompiler.extract_map.copy()
+    extract_map.update({
+        'month': '%m',
+        'day': '%d',
+        'year': '%Y',
+        'second': '%S',
+        'hour': '%H',
+        'doy': '%j',
+        'minute': '%M',
+        'epoch': '%s',
+        'dow': '%w',
+        'week': '%W'
+    })
+
     def visit_cast(self, cast, **kwargs):
         if self.dialect.supports_cast:
             return super(SQLiteCompiler, self).visit_cast(cast)
         else:
             return self.process(cast.clause)
 
+    def visit_extract(self, extract):
+        try:
+            return "CAST(STRFTIME('%s', %s) AS INTEGER)" % (
+                self.extract_map[extract.field], self.process(extract.expr))
+        except KeyError:
+            raise exc.ArgumentError(
+                "%s is not a valid extract argument." % extract.field)
+
     def limit_clause(self, select):
         text = ""
         if select._limit is not None:
diff --git a/lib/sqlalchemy/databases/sybase.py b/lib/sqlalchemy/databases/sybase.py
index 6007315f2..f5b48e147 100644
--- a/lib/sqlalchemy/databases/sybase.py
+++ b/lib/sqlalchemy/databases/sybase.py
@@ -733,6 +733,14 @@ class SybaseSQLCompiler(compiler.DefaultCompiler):
         sql_operators.mod: lambda x, y: "MOD(%s, %s)" % (x, y),
     })
 
+    extract_map = compiler.DefaultCompiler.extract_map.copy()
+    extract_map.update ({
+        'doy': 'dayofyear',
+        'dow': 'weekday',
+        'milliseconds': 'millisecond'
+    })
+
+
     def bindparam_string(self, name):
         res = super(SybaseSQLCompiler, self).bindparam_string(name)
         if name.lower().startswith('literal'):
@@ -786,6 +794,10 @@ class SybaseSQLCompiler(compiler.DefaultCompiler):
             res = "CAST(%s AS %s)" % (res, self.process(cast.typeclause))
         return res
 
+    def visit_extract(self, extract):
+        field = self.extract_map.get(extract.field, extract.field)
+        return 'DATEPART("%s", %s)' % (field, self.process(extract.expr))
+
     def for_update_clause(self, select):
         # "FOR UPDATE" is only allowed on "DECLARE CURSOR" which SQLAlchemy doesn't use
         return ''