- Added new MySQL-specific :class:`.mysql.DATETIME` which includes

fractional seconds support; also added fractional seconds support
to :class:`.mysql.TIMESTAMP`.  DBAPI support is limited, though
fractional seconds are known to be supported by MySQL Connector/Python.
Patch courtesy Geert JM Vanderkelen. #2941

3 files changed, 108 insertions, 20 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 531c7c545..c1fcdf36a 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -12,6 +12,17 @@
     :version: 0.8.5
 
      .. change::
+        :tags: feature, mysql
+        :versions: 0.9.3
+        :tickets: 2941
+
+        Added new MySQL-specific :class:`.mysql.DATETIME` which includes
+        fractional seconds support; also added fractional seconds support
+        to :class:`.mysql.TIMESTAMP`.  DBAPI support is limited, though
+        fractional seconds are known to be supported by MySQL Connector/Python.
+        Patch courtesy Geert JM Vanderkelen.
+
+     .. change::
         :tags: bug, mysql
         :versions: 0.9.3
         :tickets: 2966
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index c557d3b6f..0880c5efa 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -336,7 +336,6 @@ reflection will not include foreign keys.  For these tables, you may supply a
 """
 
 import datetime
-import inspect
 import re
 import sys
 
@@ -349,7 +348,7 @@ from ...engine import reflection
 from ...engine import default
 from ... import types as sqltypes
 from ...util import topological
-from ...types import DATE, DATETIME, BOOLEAN, TIME, \
+from ...types import DATE, BOOLEAN, \
                                 BLOB, BINARY, VARBINARY
 
 RESERVED_WORDS = set(
@@ -763,15 +762,7 @@ class BIT(sqltypes.TypeEngine):
 
 
 class TIME(sqltypes.TIME):
-    """MySQL TIME type.
-
-    Recent versions of MySQL add support for
-    fractional seconds precision.   While the
-    :class:`.mysql.TIME` type now supports this,
-    note that many DBAPI drivers may not yet
-    include support.
-
-    """
+    """MySQL TIME type. """
 
     __visit_name__ = 'TIME'
 
@@ -782,9 +773,13 @@ class TIME(sqltypes.TIME):
         :param fsp: fractional seconds precision value.
          MySQL 5.6 supports storage of fractional seconds;
          this parameter will be used when emitting DDL
-         for the TIME type.  Note that many DBAPI drivers
-         may not yet have support for fractional seconds,
-         however.
+         for the TIME type.
+
+         .. note::
+
+            DBAPI driver support for fractional seconds may
+            be limited; current support includes
+            MySQL Connector/Python.
 
         .. versionadded:: 0.8 The MySQL-specific TIME
            type as well as fractional seconds support.
@@ -812,9 +807,64 @@ class TIME(sqltypes.TIME):
 
 
 class TIMESTAMP(sqltypes.TIMESTAMP):
-    """MySQL TIMESTAMP type."""
+    """MySQL TIMESTAMP type.
+
+    """
+
     __visit_name__ = 'TIMESTAMP'
 
+    def __init__(self, timezone=False, fsp=None):
+        """Construct a MySQL TIMESTAMP type.
+
+        :param timezone: not used by the MySQL dialect.
+        :param fsp: fractional seconds precision value.
+         MySQL 5.6.4 supports storage of fractional seconds;
+         this parameter will be used when emitting DDL
+         for the TIMESTAMP type.
+
+         .. note::
+
+            DBAPI driver support for fractional seconds may
+            be limited; current support includes
+            MySQL Connector/Python.
+
+        .. versionadded:: 0.8.5 Added MySQL-specific :class:`.mysql.TIMESTAMP`
+           with fractional seconds support.
+
+        """
+        super(TIMESTAMP, self).__init__(timezone=timezone)
+        self.fsp = fsp
+
+
+class DATETIME(sqltypes.DATETIME):
+    """MySQL DATETIME type.
+
+    """
+
+    __visit_name__ = 'DATETIME'
+
+    def __init__(self, timezone=False, fsp=None):
+        """Construct a MySQL DATETIME type.
+
+        :param timezone: not used by the MySQL dialect.
+        :param fsp: fractional seconds precision value.
+         MySQL 5.6.4 supports storage of fractional seconds;
+         this parameter will be used when emitting DDL
+         for the DATETIME type.
+
+         .. note::
+
+            DBAPI driver support for fractional seconds may
+            be limited; current support includes
+            MySQL Connector/Python.
+
+        .. versionadded:: 0.8.5 Added MySQL-specific :class:`.mysql.DATETIME`
+           with fractional seconds support.
+
+        """
+        super(DATETIME, self).__init__(timezone=timezone)
+        self.fsp = fsp
+
 
 class YEAR(sqltypes.TypeEngine):
     """MySQL YEAR type, for single byte storage of years 1901-2155."""
@@ -1887,7 +1937,10 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
             return "BIT"
 
     def visit_DATETIME(self, type_):
-        return "DATETIME"
+        if getattr(type_, 'fsp', None):
+            return "DATETIME(%d)" % type_.fsp
+        else:
+            return "DATETIME"
 
     def visit_DATE(self, type_):
         return "DATE"
@@ -1899,7 +1952,10 @@ class MySQLTypeCompiler(compiler.GenericTypeCompiler):
             return "TIME"
 
     def visit_TIMESTAMP(self, type_):
-        return 'TIMESTAMP'
+        if getattr(type_, 'fsp', None):
+            return "TIMESTAMP(%d)" % type_.fsp
+        else:
+            return "TIMESTAMP"
 
     def visit_YEAR(self, type_):
         if type_.display_width is None:
diff --git a/test/dialect/mysql/test_types.py b/test/dialect/mysql/test_types.py
index acf9c1e2f..a0549e374 100644
--- a/test/dialect/mysql/test_types.py
+++ b/test/dialect/mysql/test_types.py
@@ -408,8 +408,14 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         roundtrip([False, False, 0, 0, 0], [False, False, False,
                   False, 0])
 
-    def test_timestamp(self):
-        """Exercise funky TIMESTAMP default syntax."""
+    def test_timestamp_fsp(self):
+        self.assert_compile(
+                mysql.TIMESTAMP(fsp=5),
+                "TIMESTAMP(5)"
+        )
+
+    def test_timestamp_defaults(self):
+        """Exercise funky TIMESTAMP default syntax when used in columns."""
 
         columns = [
             ([TIMESTAMP],
@@ -473,7 +479,20 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
             [(now, now), (None, now)]
         )
 
-    def test_time(self):
+    def test_datetime_generic(self):
+        self.assert_compile(
+                mysql.DATETIME(),
+                "DATETIME"
+        )
+
+    def test_datetime_fsp(self):
+        self.assert_compile(
+                mysql.DATETIME(fsp=4),
+                "DATETIME(4)"
+        )
+
+
+    def test_time_generic(self):
         """"Exercise TIME."""
 
         self.assert_compile(
@@ -481,11 +500,13 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
                 "TIME"
         )
 
+    def test_time_fsp(self):
         self.assert_compile(
                 mysql.TIME(fsp=5),
                 "TIME(5)"
         )
 
+    def test_time_result_processor(self):
         eq_(
             mysql.TIME().result_processor(None, None)(
                     datetime.timedelta(seconds=35, minutes=517,