You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
cx_Oracle does not correctly convert TIMESTAMP WITH TIMEZONE #13
cx_Oracle 5.3 does not correctly handle TIMESTAMP WITH TIMEZONE
When a TIMESTAMP WITH TIMEZONE is returned from a query and converted to a Python datetime object, its time zone information is simply truncated.
Simple demonstration, where current_timestamp and systimestamp are in different time zones, and the Python objects returned for them are totally lacking in time zone information:
The conversion in the other direction is broken as well.
When marshalling a Python timezone-aware datetime object into a bind variable… the bind variable ends up as a naïve Oracle DATE rather than a TIMESTAMP WITH TIME ZONE:
>>>fromdatetimeimportdatetime, timezone, timedelta>>>tzdate=datetime.now(tz=timezone(timedelta(hours=-1)))
>>>print(tzdate.isoformat())
'2017-04-19T22:43:53.189561-01:00'>>>print( cur.execute("select to_char(:tzdate,'YYYY-MM-DD\"T\"HH24:MM:SS') from dual",
{'tzdate':tzdate}).fetchone() )
('2017-04-19T22:04:53',)
>>>cur.execute("select extract(timezone_hour from :tzdate) from dual", {'tzdate':tzdate}).fetchone()
DatabaseError: ORA-30076: invalidextractfieldforextractsource
No attempt has currently been made to transfer timezone information either to or from the database. This is primarily due to the fact that there is still no method in the C API to acquire or set the tzinfo attribute on datetime instances. That would imply the need to acquire the attribute using the generic C API as well as create datetime instances with the generic C API which will eliminate the benefits of using the datetime C API in the first place. I'll do some checking to see if there is a solution to this that doesn't involve a significant reduction in performance.
No attempt has currently been made to transfer timezone information either to or from the database. This is primarily due to the fact that there is still no method in the C API to acquire or set the tzinfo attribute on datetime instances. That would imply the need to acquire the attribute using the generic C API as well as create datetime instances with the generic C API which will eliminate the benefits of using the datetime C API in the first place.
Argghh, I see what you mean. Python datetime C API has no mechanism to manipulate tzinfo. ☹️. I guess that's because the tzinfo objects are typically "heavyweight" Python objects, rather than something that can be easily manipulated from C.
I'll do some checking to see if there is a solution to this that doesn't involve a significant reduction in performance.
Perhaps you could create datetime instances using the C API and then use the generic API to call dt.replace(tzinfo=...) to add the time zone? I'm not sure if this is actually any faster.
Drat. A quick test suggests that using .replace on a pre-existing datetime object is actually slower than creating a new datetime object using the generic API.
In [20]: %%timeit-n1000000tz=timezone(timedelta(hours=-1))
...: # Create a new datetime object using Python API
...: now=datetime(2017,4,20,12,0,0,0,tz)
...:
1000000loops, bestof3: 237nsperloopIn [21]: %%timeit-n1000000now=datetime.now(); tz=timezone(timedelta(hours=-1))
...: # Replace the tzinfo attribute (and maybe garbage-collect the old 'now' object)
...: now=now.replace(tzinfo=tz)
...:
1000000loops, bestof3: 1.33µsperloop
I just ran my own set of tests using the existing C API and then a second set calling the datetime.datetime() constructor instead (using PyObject_Call). This is for calling var.getvalue() on a datetime variable created used the cursor.var() method. The results are not good. The cost of creating the tuple and invoking the call is 4.5x greater than using the existing C API (124 ns for one and 558 ns for the other). It looks like the only viable approach is to request that the tzinfo attribute be made available in the Python core, or to find some way to work around the deficiency. Thoughts?
I just ran my own set of tests using the existing C API and then a second set calling the datetime.datetime() constructor instead (using PyObject_Call). This is for calling var.getvalue() on a datetime variable created used the cursor.var() method. The results are not good. The cost of creating the tuple and invoking the call is 4.5x greater than using the existing C API (124 ns for one and 558 ns for the other).
First off, thank you for digging into this, I really appreciate it!
It looks like the only viable approach is to request that the tzinfo attribute be made available in the Python core…
Yeah, that seems like a good idea to request it.
Even if it were possible to get/set the tzinfo from the C API, I can think of a couple further issues:
You would still have to create tzinfo objects using the Python API. (Presumably, most result sets don't include an infinite number of time zones, so you could probably cache one Python tzinfo object for each Oracle TIMEZONE and get a significant speedup that way.)
There are (at least) 2 kinds of Oracle time zones that I am aware of, along with 2 corresponding kinds of Python time zone objects:
"Naive" time zones with a fixed hour/minute offset. I think it should be straightforward to add a C API to create one of these, similar to PyDelta_FromDSU.
with naive_timestamp as (selectcurrent_timestamp ts from dual)
select extract(timezone_hour from ts), extract(timezone_minute from ts), extract(timezone_region from ts), extract(timezone_abbr from ts) from naive_timestamp;
→ returns -7, 0, 'UNKNOWN', 'UNK' for me
"Fancy" time zone based on the TZ database, which keep track of daylight savings changes and all that mess. In Python, these are implemented in pytz, which isn't part of the standard library… so they'd need to be created with the generic API.
with fancy_timestamp as (selectcurrent_timestamp at time zone 'America/Los_Angeles' ts from dual)
select extract(timezone_hour from ts), extract(timezone_minute from ts), extract(timezone_region from ts), extract(timezone_abbr from ts) from naive_timestamp;
→ returns -7, 0, 'America/Los_Angeles', 'PDT' for me
Fortunately, both Oracle and Python use the same standard TZ database naming for the "fancy" timezones, so I think it should be relatively straightforward to convert between them.
You would still have to create tzinfo objects using the Python API. (Presumably, most result sets don't include an infinite number of time zones, so you could probably cache one Python tzinfo object for each Oracle TIMEZONE and get a significant speedup that way.)
Yes, a cache of tzinfo objects would definitely work so the lack of a C API for creating tzinfo objects is not as much of a concern.
"Naive" time zones with a fixed hour/minute offset. I think it should be straightforward to add a C API to create one of these, similar to PyDelta_FromDSU.
These are straightforward as the hour and minute offset are readily available.
"Fancy" time zone based on the TZ database, which keep track of daylight savings changes and all that mess. In Python, these are implemented in pytz, which isn't part of the standard library… so they'd need to be created with the generic API.
I'd hesitate to use these since they aren't part of the standard library. Perhaps a tzinfo class could be created by cx_Oracle which uses the time zone name and offsets available from OCI?
"Fancy" time zone based on the TZ database, which keep track of daylight savings changes and all that mess. In Python, these are implemented in pytz, which isn't part of the standard library… so they'd need to be created with the generic API.
I'd hesitate to use these since they aren't part of the standard library. Perhaps a tzinfo class could be created by cx_Oracle which uses the time zone name and offsets available from OCI?
Yeah, that seems like a good idea. Actually, the "naive" time zone (datetime.timezone) has an optional name parameter (timezone(timedelta(hours=X, minutes=Y), name='ABC')) so I think it could be used as-is.
"Fancy" time zone based on the TZ database, which keep track of daylight savings changes and all that mess. In Python, these are implemented in pytz, which isn't part of the standard library… so they'd need to be created with the generic API.
I'd hesitate to use these since they aren't part of the standard library. Perhaps a tzinfo class could be created by cx_Oracle which uses the time zone name and offsets available from OCI?
Yeah, that seems like a good idea. Actually, the "naive" time zone (datetime.timezone) has an optional name parameter (timezone(timedelta(hours=X, minutes=Y), name='ABC')) so I think it could be used as-is.
That seems reasonable. Thanks for pointing that out.
No progress as I am waiting on this Python enhancement and nothing has been happening on that front, unfortunately. It would be possible to use the straightforward Python API, but there would be significant overhead in doing so, so I have avoided doing so for now.
The Python enhancement has been merged to master. Note that there is one part of the fix that can be made in cx_Oracle even without the change:
An official constructor for a timezone-aware datetime, which I think basically exists in the form of PyDatetime_CAPI->PyDateTimeAPI->DateTime_FromDateAndTime / ->DateTime_FromDateAndTimeAndFold, and we just need to document it. I think this is basically a separate issue, and I have opened #39604 to track it.
Yes, I saw that. That's great! The interesting part is that I need to support older versions of Python as well for some time to come. Once I get a copy of 3.10 with the enhancement in place I'll see about implementing the necessary changes in cx_Oracle.
Any progress here, My solution was to convert everything into UTC and add a timezone column for converting back. Not very elegant.
The best solutions I've found (combining human-readable, standards-based, and "no corner cases") are to convert timestamps to ISO8601-format string representations before transferring across the Python-to-Oracle boundary. I've basically used versions of these formulas in all of my cx_Oracle code.
Extracting a TIMESTAMP WITH TIMEZONE column in a cursor
Activity
anthony-tuininga commentedon Apr 20, 2017
No attempt has currently been made to transfer timezone information either to or from the database. This is primarily due to the fact that there is still no method in the C API to acquire or set the tzinfo attribute on datetime instances. That would imply the need to acquire the attribute using the generic C API as well as create datetime instances with the generic C API which will eliminate the benefits of using the datetime C API in the first place. I'll do some checking to see if there is a solution to this that doesn't involve a significant reduction in performance.
dlenski commentedon Apr 20, 2017
Argghh, I see what you mean. Python☹️ . I guess that's because the
datetimeC API has no mechanism to manipulatetzinfo.tzinfoobjects are typically "heavyweight" Python objects, rather than something that can be easily manipulated from C.Perhaps you could create
datetimeinstances using the C API and then use the generic API to calldt.replace(tzinfo=...)to add the time zone? I'm not sure if this is actually any faster.dlenski commentedon Apr 20, 2017
Drat. A quick test suggests that using
.replaceon a pre-existingdatetimeobject is actually slower than creating a newdatetimeobject using the generic API.anthony-tuininga commentedon Apr 20, 2017
I just ran my own set of tests using the existing C API and then a second set calling the datetime.datetime() constructor instead (using PyObject_Call). This is for calling var.getvalue() on a datetime variable created used the cursor.var() method. The results are not good. The cost of creating the tuple and invoking the call is 4.5x greater than using the existing C API (124 ns for one and 558 ns for the other). It looks like the only viable approach is to request that the tzinfo attribute be made available in the Python core, or to find some way to work around the deficiency. Thoughts?
dlenski commentedon Apr 20, 2017
First off, thank you for digging into this, I really appreciate it!
Yeah, that seems like a good idea to request it.
Even if it were possible to get/set the
tzinfofrom the C API, I can think of a couple further issues:You would still have to create
tzinfoobjects using the Python API. (Presumably, most result sets don't include an infinite number of time zones, so you could probably cache one Pythontzinfoobject for each OracleTIMEZONEand get a significant speedup that way.)There are (at least) 2 kinds of Oracle time zones that I am aware of, along with 2 corresponding kinds of Python time zone objects:
PyDelta_FromDSU.pytz, which isn't part of the standard library… so they'd need to be created with the generic API.Fortunately, both Oracle and Python use the same standard TZ database naming for the "fancy" timezones, so I think it should be relatively straightforward to convert between them.
anthony-tuininga commentedon Apr 24, 2017
I've added a Python enhancement to track it.
Yes, a cache of tzinfo objects would definitely work so the lack of a C API for creating tzinfo objects is not as much of a concern.
These are straightforward as the hour and minute offset are readily available.
I'd hesitate to use these since they aren't part of the standard library. Perhaps a tzinfo class could be created by cx_Oracle which uses the time zone name and offsets available from OCI?
dlenski commentedon Apr 24, 2017
Excellent.
Yeah, that seems like a good idea. Actually, the "naive" time zone (
datetime.timezone) has an optional name parameter (timezone(timedelta(hours=X, minutes=Y), name='ABC')) so I think it could be used as-is.anthony-tuininga commentedon Apr 24, 2017
That seems reasonable. Thanks for pointing that out.
fenchu commentedon Jun 25, 2019
Any progress here, My solution was to convert everything into UTC and add a timezone column for converting back. Not very elegant.
anthony-tuininga commentedon Jun 25, 2019
No progress as I am waiting on this Python enhancement and nothing has been happening on that front, unfortunately. It would be possible to use the straightforward Python API, but there would be significant overhead in doing so, so I have avoided doing so for now.
sebukoleth commentedon Sep 23, 2020
The Python enhancement has been merged to master. Note that there is one part of the fix that can be made in cx_Oracle even without the change:
anthony-tuininga commentedon Sep 23, 2020
Yes, I saw that. That's great! The interesting part is that I need to support older versions of Python as well for some time to come. Once I get a copy of 3.10 with the enhancement in place I'll see about implementing the necessary changes in cx_Oracle.
dlenski commentedon Sep 23, 2020
Glad to hear there's some progress on the Python API side now. 👍 👍
anthony-tuininga commentedon Sep 23, 2020
Indeed!
dlenski commentedon Sep 23, 2020
In the meantime…
The best solutions I've found (combining human-readable, standards-based, and "no corner cases") are to convert timestamps to ISO8601-format string representations before transferring across the Python-to-Oracle boundary. I've basically used versions of these formulas in all of my cx_Oracle code.
Extracting a
TIMESTAMP WITH TIMEZONEcolumn in a cursorInjecting a
TIMESTAMP WITH TIMEZONEas a bind variable