From a37218a8e0fa03acd0eef2d57e0294cd6e96fa84 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Wed, 11 Jan 2023 20:44:07 +0100 Subject: [PATCH] Support local timespamp support on Oracle Added support for the Oracle SQL type ``TIMESTAMP WITH LOCAL TIME ZONE``, using a newly added Oracle-specific :class:`_oracle.TIMESTAMP` datatype. Fixes: #9086 Change-Id: Ib14119503a8aaf20e1aa4e36be80ccca37383e90 --- doc/build/changelog/unreleased_20/9086.rst | 6 +++++ doc/build/dialects/oracle.rst | 3 +++ lib/sqlalchemy/dialects/oracle/base.py | 9 +++++-- lib/sqlalchemy/dialects/oracle/types.py | 28 ++++++++++++++++++++++ test/dialect/oracle/test_types.py | 7 ++++-- 5 files changed, 49 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/9086.rst diff --git a/doc/build/changelog/unreleased_20/9086.rst b/doc/build/changelog/unreleased_20/9086.rst new file mode 100644 index 0000000000..55031d98b5 --- /dev/null +++ b/doc/build/changelog/unreleased_20/9086.rst @@ -0,0 +1,6 @@ +.. change:: + :tags: oracle, usecase + :tickets: 9086 + + Added support for the Oracle SQL type ``TIMESTAMP WITH LOCAL TIME ZONE``, + using a newly added Oracle-specific :class:`_oracle.TIMESTAMP` datatype. diff --git a/doc/build/dialects/oracle.rst b/doc/build/dialects/oracle.rst index d676f633b9..02f5122141 100644 --- a/doc/build/dialects/oracle.rst +++ b/doc/build/dialects/oracle.rst @@ -65,6 +65,9 @@ construction arguments, are as follows: .. autoclass:: RAW :members: __init__ +.. autoclass:: TIMESTAMP + :members: __init__ + .. _cx_oracle: cx_Oracle diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index d24df7ee00..65377be286 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -564,6 +564,7 @@ from .types import NVARCHAR2 # noqa from .types import OracleRaw # noqa from .types import RAW from .types import ROWID # noqa +from .types import TIMESTAMP from .types import VARCHAR2 # noqa from ... import Computed from ... import exc @@ -595,7 +596,6 @@ from ...types import INTEGER from ...types import NCHAR from ...types import NVARCHAR from ...types import REAL -from ...types import TIMESTAMP from ...types import VARCHAR RESERVED_WORDS = set( @@ -635,6 +635,7 @@ ischema_names = { "NCLOB": NCLOB, "TIMESTAMP": TIMESTAMP, "TIMESTAMP WITH TIME ZONE": TIMESTAMP, + "TIMESTAMP WITH LOCAL TIME ZONE": TIMESTAMP, "INTERVAL DAY TO SECOND": INTERVAL, "RAW": RAW, "FLOAT": FLOAT, @@ -681,7 +682,9 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler): return "LONG" def visit_TIMESTAMP(self, type_, **kw): - if type_.timezone: + if getattr(type_, "local_timezone", False): + return "TIMESTAMP WITH LOCAL TIME ZONE" + elif type_.timezone: return "TIMESTAMP WITH TIME ZONE" else: return "TIMESTAMP" @@ -2330,6 +2333,8 @@ class OracleDialect(default.DefaultDialect): coltype = self.ischema_names.get(coltype)(char_length) elif "WITH TIME ZONE" in coltype: coltype = TIMESTAMP(timezone=True) + elif "WITH LOCAL TIME ZONE" in coltype: + coltype = TIMESTAMP(local_timezone=True) else: coltype = re.sub(r"\(\d+\)", "", coltype) try: diff --git a/lib/sqlalchemy/dialects/oracle/types.py b/lib/sqlalchemy/dialects/oracle/types.py index af66d2eb48..db3d57228a 100644 --- a/lib/sqlalchemy/dialects/oracle/types.py +++ b/lib/sqlalchemy/dialects/oracle/types.py @@ -5,6 +5,7 @@ # the MIT License: https://www.opensource.org/licenses/mit-license.php # mypy: ignore-errors +from ... import exc from ...sql import sqltypes from ...types import NVARCHAR from ...types import VARCHAR @@ -216,6 +217,33 @@ class INTERVAL(sqltypes.NativeForEmulated, sqltypes._AbstractInterval): ) +class TIMESTAMP(sqltypes.TIMESTAMP): + """Oracle implementation of ``TIMESTAMP``, which supports additional + Oracle-specific modes + + .. versionadded:: 2.0 + + """ + + def __init__(self, timezone: bool = False, local_timezone: bool = False): + """Construct a new :class:`_oracle.TIMESTAMP`. + + :param timezone: boolean. Indicates that the TIMESTAMP type should + use Oracle's ``TIMESTAMP WITH TIME ZONE`` datatype. + + :param local_timezone: boolean. Indicates that the TIMESTAMP type + should use Oracle's ``TIMESTAMP WITH LOCAL TIME ZONE`` datatype. + + + """ + if timezone and local_timezone: + raise exc.ArgumentError( + "timezone and local_timezone are mutually exclusive" + ) + super().__init__(timezone=timezone) + self.local_timezone = local_timezone + + class ROWID(sqltypes.TypeEngine): """Oracle ROWID type. diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py index 2ba42f5840..e77bde19e9 100644 --- a/test/dialect/oracle/test_types.py +++ b/test/dialect/oracle/test_types.py @@ -752,6 +752,7 @@ class TypesTest(fixtures.TestBase): Column("d3", TIMESTAMP), Column("d4", TIMESTAMP(timezone=True)), Column("d5", oracle.INTERVAL(second_precision=5)), + Column("d6", oracle.TIMESTAMP(local_timezone=True)), ) metadata.create_all(connection) m = MetaData() @@ -760,10 +761,12 @@ class TypesTest(fixtures.TestBase): assert isinstance(t1.c.d1.type, DateTime) assert isinstance(t1.c.d2.type, oracle.DATE) assert isinstance(t1.c.d2.type, DateTime) - assert isinstance(t1.c.d3.type, TIMESTAMP) + assert isinstance(t1.c.d3.type, oracle.TIMESTAMP) assert not t1.c.d3.type.timezone - assert isinstance(t1.c.d4.type, TIMESTAMP) + assert isinstance(t1.c.d4.type, oracle.TIMESTAMP) assert t1.c.d4.type.timezone + assert isinstance(t1.c.d6.type, oracle.TIMESTAMP) + assert t1.c.d6.type.local_timezone assert isinstance(t1.c.d5.type, oracle.INTERVAL) def _dont_test_reflect_all_types_schema(self): -- 2.47.2