Merge "Add DATETIMEOFFSET support for mssql+pyodbc"

3 files changed, 152 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index f12fb5ead..954632eb2 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -118,10 +118,13 @@ in order to use this flag::
 
 """  # noqa
 
+import datetime
 import decimal
 import re
+import struct
 
 from .base import BINARY
+from .base import DATETIMEOFFSET
 from .base import MSDialect
 from .base import MSExecutionContext
 from .base import VARBINARY
@@ -226,6 +229,17 @@ class _ms_binary_pyodbc(object):
         return process
 
 
+class _ODBCDateTimeOffset(DATETIMEOFFSET):
+    def bind_processor(self, dialect):
+        def process(value):
+            """Convert to string format required by T-SQL."""
+            dto_string = value.strftime("%Y-%m-%d %H:%M:%S %z")
+            # offset needs a colon, e.g., -0700 -> -07:00
+            return dto_string[:23] + ":" + dto_string[23:]
+
+        return process
+
+
 class _VARBINARY_pyodbc(_ms_binary_pyodbc, VARBINARY):
     pass
 
@@ -294,6 +308,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
             sqltypes.Numeric: _MSNumeric_pyodbc,
             sqltypes.Float: _MSFloat_pyodbc,
             BINARY: _BINARY_pyodbc,
+            DATETIMEOFFSET: _ODBCDateTimeOffset,
             # SQL Server dialect has a VARBINARY that is just to support
             # "deprecate_large_types" w/ VARBINARY(max), but also we must
             # handle the usual SQL standard VARBINARY
@@ -345,6 +360,39 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
                     pass
             return tuple(version)
 
+    def on_connect(self):
+        super_ = super(MSDialect_pyodbc, self).on_connect()
+
+        def on_connect(conn):
+            if super_ is not None:
+                super_(conn)
+
+            self._setup_timestampoffset_type(conn)
+
+        return on_connect
+
+    def _setup_timestampoffset_type(self, connection):
+        # output converter function for datetimeoffset
+        def _handle_datetimeoffset(dto_value):
+            tup = struct.unpack("<6hI2h", dto_value)
+            return datetime.datetime(
+                tup[0],
+                tup[1],
+                tup[2],
+                tup[3],
+                tup[4],
+                tup[5],
+                tup[6] // 1000,
+                util.timezone(
+                    datetime.timedelta(hours=tup[7], minutes=tup[8])
+                ),
+            )
+
+        odbc_SQL_SS_TIMESTAMPOFFSET = -155  # as defined in SQLNCLI.h
+        connection.add_output_converter(
+            odbc_SQL_SS_TIMESTAMPOFFSET, _handle_datetimeoffset
+        )
+
     def do_executemany(self, cursor, statement, parameters, context=None):
         if self.fast_executemany:
             cursor.fast_executemany = True
diff --git a/lib/sqlalchemy/util/__init__.py b/lib/sqlalchemy/util/__init__.py
index a8cdc5ef7..78155b08a 100644
--- a/lib/sqlalchemy/util/__init__.py
+++ b/lib/sqlalchemy/util/__init__.py
@@ -79,6 +79,7 @@ from .compat import string_types  # noqa
 from .compat import StringIO  # noqa
 from .compat import text_type  # noqa
 from .compat import threading  # noqa
+from .compat import timezone  # noqa
 from .compat import u  # noqa
 from .compat import ue  # noqa
 from .compat import unquote  # noqa
diff --git a/lib/sqlalchemy/util/compat.py b/lib/sqlalchemy/util/compat.py
index 8cfaea26e..d39f42479 100644
--- a/lib/sqlalchemy/util/compat.py
+++ b/lib/sqlalchemy/util/compat.py
@@ -411,3 +411,106 @@ def with_metaclass(meta, *bases):
             return meta(name, bases, d)
 
     return metaclass("temporary_class", None, {})
+
+
+if py3k:
+    from datetime import timezone
+else:
+    from datetime import datetime
+    from datetime import timedelta
+    from datetime import tzinfo
+
+    class timezone(tzinfo):
+        """Minimal port of python 3 timezone object"""
+
+        __slots__ = "_offset"
+
+        def __init__(self, offset):
+            if not isinstance(offset, timedelta):
+                raise TypeError("offset must be a timedelta")
+            if not self._minoffset <= offset <= self._maxoffset:
+                raise ValueError(
+                    "offset must be a timedelta "
+                    "strictly between -timedelta(hours=24) and "
+                    "timedelta(hours=24)."
+                )
+            self._offset = offset
+
+        def __eq__(self, other):
+            if type(other) != timezone:
+                return False
+            return self._offset == other._offset
+
+        def __hash__(self):
+            return hash(self._offset)
+
+        def __repr__(self):
+            return "sqlalchemy.util.%s(%r)" % (
+                self.__class__.__name__,
+                self._offset,
+            )
+
+        def __str__(self):
+            return self.tzname(None)
+
+        def utcoffset(self, dt):
+            return self._offset
+
+        def tzname(self, dt):
+            return self._name_from_offset(self._offset)
+
+        def dst(self, dt):
+            return None
+
+        def fromutc(self, dt):
+            if isinstance(dt, datetime):
+                if dt.tzinfo is not self:
+                    raise ValueError("fromutc: dt.tzinfo " "is not self")
+                return dt + self._offset
+            raise TypeError(
+                "fromutc() argument must be a datetime instance" " or None"
+            )
+
+        @staticmethod
+        def _timedelta_to_microseconds(timedelta):
+            """backport of timedelta._to_microseconds()"""
+            return (
+                timedelta.days * (24 * 3600) + timedelta.seconds
+            ) * 1000000 + timedelta.microseconds
+
+        @staticmethod
+        def _divmod_timedeltas(a, b):
+            """backport of timedelta.__divmod__"""
+
+            q, r = divmod(
+                timezone._timedelta_to_microseconds(a),
+                timezone._timedelta_to_microseconds(b),
+            )
+            return q, timedelta(0, 0, r)
+
+        @staticmethod
+        def _name_from_offset(delta):
+            if not delta:
+                return "UTC"
+            if delta < timedelta(0):
+                sign = "-"
+                delta = -delta
+            else:
+                sign = "+"
+            hours, rest = timezone._divmod_timedeltas(
+                delta, timedelta(hours=1)
+            )
+            minutes, rest = timezone._divmod_timedeltas(
+                rest, timedelta(minutes=1)
+            )
+            result = "UTC%s%02d:%02d" % (sign, hours, minutes)
+            if rest.seconds:
+                result += ":%02d" % (rest.seconds,)
+            if rest.microseconds:
+                result += ".%06d" % (rest.microseconds,)
+            return result
+
+        _maxoffset = timedelta(hours=23, minutes=59)
+        _minoffset = -_maxoffset
+
+    timezone.utc = timezone(timedelta(0))