Add support for named time zones in thin mode #20
Comments
|
This is an Oracle Client library issue. We saw the same problem once in the past for cx_Oracle with an application using timezone v35 files. There was no problem when that application used v36 files. We logged it as Oracle bug 33576821, which is still open - since there was a simple solution. If you can share any other details such as machine timezone settings and environment variables I can add it to the bug to help identify what triggers it. Some thoughts:
|
|
I've done some more testing and found that the timestamp with time zone works correctly when ORA_TZFILE is the same on server and client. I had similar problems with cx_Oracle and posted there a question oracle/python-cx_Oracle#631 I couldn't find v36 files. Older versions I found in official docker images. @cjbj could you point me where I could the v36 from? Maybe v36 in client would work correctly with older server time zone versions. Interesting is that with client 21 I was able to connect to server 11 although the official OCI download page says different
Below OCI 21.6 |
|
What I have discovered is that everything works as expected with numeric timezone data (-05:00 instead of US/Central, for example). The thin driver doesn't handle named time zones properly either -- so that needs to be addressed (either raising an error like the thick driver or something better than that if possible!). The problem with using named time zones is that everyone has to agree on what they mean. I'll ask internally if there is a way to mitigate this. |
|
The v36 files were probably from an internal build. Regarding 21->11 connectivity, there's sometimes a difference between what's technically possibly vs what is 'supported' in the tested & "you can call us if there is a problem" way. |
|
@anthony-tuininga @cjbj are we in agreement that currently the only viable work-around is to use numerical hour offset? |
|
@agilevic Any decision on named timezones is still pending. And so is that bug - sorry. |
|
@agilevic, the current viable workaround for the issue is to use numerical time zone offsets when using thin mode. I will probably add an exception for version 1.2.3 and will then discuss further what to do for version 1.3 -- watch this space! |
|
Thank you, both. For what it's worth our workaround has been to cast TIMESTAMP WITH TIMEZONE, which might be required in query logic (join) to regular TIMESTAMP in the SELECT clause for the output. In many cases this is sufficient in the client application, but of course only covers some use cases and can slow down query performance. |
|
Having the same issue using thick mode.
the code import sys
import os
import platform
import oracledb
import pandas as pd
import sqlalchemy
import warnings
warnings.filterwarnings("ignore")
class OracleConnector(object):
def __init__(self, user : str = None, password : str = None, host : str = None, port : int = None, service_name : str = None, lib_dir : str = None):
self.user = user
self.password = password
self.host = host
self.port = port
self.service_name = service_name
self.thick_mode = {"lib_dir": lib_dir}
if (self.user is None):
self.user = 'test'
if (self.password is None):
self.password = 'test'
if (self.host is None):
self.host = '127.0.0.1'
if (self.port is None):
self.port = 1521
if (self.service_name is None):
self.service_name = 'xe'
# if (self.thick_mode['lib_dir'] is not None):
# oracledb.init_oracle_client(lib_dir=self.thick_mode['lib_dir'])
def run_query(self, query):
engine = sqlalchemy.create_engine(
f'oracle+oracledb://{self.user}:{self.password}@{self.host}:{self.port}/?service_name={self.service_name}',
thick_mode=self.thick_mode)
with engine.connect() as connection:
# with oracledb.connect(user = self.user, password = self.password,
# host = self.host, port = self.port,
# service_name = self.service_name) as connection:
return pd.read_sql(query, con = connection)
def main():
x = OracleConnector()
sql = """
select to_timestamp_tz('2023-07-05 02:00:00.000000000 America/Montreal', 'YYYY-MM-DD HH24:MI:SS.FF9 TZR') x from dual
"""
df = x.run_query(sql)
print(df)
if __name__ == '__main__':
main() |
|
I ran into this issue. Upgrade from oracle_cx. It is not possible to update the oracle database as it is serving a legacy application. Any solution on how to fix this? |
|
You can use thick mode to fetch timestamp with timezone using named time zones or you can use numeric offsets. The support for named time zones in thin mode is on a long list of enhancement requests! |
anthony-tuininga
added
enhancement
anthony-tuininga
changed the title
|
I've changed this to an enhancement request so it can be tracked. |
|
Not working in thick mode either. got the error: I am not sure what you mean by numeric offset. if you can please give an example. I tried changing timestamp to Numeric in the ORM schema, got the error: |
|
Full traceback Using oracledb 2.0.1 |
|
The error |
|
As a workaround, what can I use to transform a timestamp to UTC? select cast(mytimestamp as timestamp) at time zone 'UTC';
select cast(mytimestamp as timestamp) at time zone '+00:00'; |
|
They should indeed be equivalent. |
|
We are using current_timestamp to update timestamp with time zone column. Trigger's code: Triggers are also defined on other tables. Updates on some fields update the column of the table A. Without the explicit conversion, the value in the column is not correct. Its format is 'DD-MM-YY HH24:MI:SSXFF TZR'. Do you think there are other alternatives to get the correct format ? |
|
@ggasnier72 presents a use case we are working on.
We are looking for a solution which will not regress with a future update of OracleDB driver. |
|
+1 |
|
Is there any update on this ? |
|
@DinnieJ no update. My general recommendation is the same: use UTC in the DB and mid-tier (e.g. if you are using Python in a service), and do conversions in the presentation layer (e.g. browser layer) |
Uh oh!
There was an error while loading. Please reload this page.
Fetching timestamp with time zone raises error
ORA-01805: possible error in date/time operationinstead of returning datetime with time zone information.What versions are you using?
Is it an error or a hang or a crash? Error
What error(s) or behavior you are seeing?
Error
oracledb.exceptions.DatabaseError: ORA-01805: possible error in date/time operationis raised when trying to useat time zonein SQL . Below is a sample code to reproduce the problem.Does your application call init_oracle_client()? Yes, uses Thick mode.
Include a runnable Python script that shows the problem.
The above is an example using systimestamp but similar error can be achieved by crating table with column type timestamp with time zone
The text was updated successfully, but these errors were encountered: