Oracle support for "TIMESTAMP WITH TIME ZONE" is broken

[ancientwizard]

My application works with DB, data, clients and timestamps from many time zones. It means that during testing conversion errors are quickly discovered. The time zone is being ignored; therefore in a test case for a timestamp inserted as "2021-05-18T08:00:00-05:00" and confirmed within Oracle will be printed as "2021-05-18T08:00:00Z" using raku; which is off by 5 hours. When .perl is used for introspection I see its formed as DateTime.new(2021,5,18,8,0,0); which is the cause of the issue as it assumes GMT(offset zero).

[ancientwizard]

Okay, so I've studied the issue a bit; I now see the It's clear that Fields that would be targeted to be implemented using DateTime DBIish makes assumptions; 1) the data is 7 bytes long the values are assumed to be something like 'YYMDHMS' 2) other wise the format must be a string that wont break the input of DateTime.new(Str); but that's asking a lot; not everyone can use/wants that standard format. This was never an issue for Perl5 BDB as it just returned the string and left it to the imagination of the consumer. You make be using ALTER SEESION TO perform some formatting but I missed it. Some possible solutions; and we don't need to do just one to give the consumer options

• make it optional that these are returned as strings and not force-feed into DateTime
• make it optional that the consumer can define the format that will be sent to Oracle and also used to carve up the string to feed Datetime; sent to Oracle means something like: setting NLS_TIMESTAMP_TZ_FORMAT NLS_TIMESTAMP_FORMAT and others
• Too bad DateTime does not also take an optional input formatter and not just the output

I'd love to discuss these and other options

[rbt]

PostgreSQL has a similar issue with timestamps. If the backend is pre-configured to output a specific format then everything works. DBIish does not attempt to request configure for a specific format when the connection is established.

I'd like to overhaul DBIish's type system to use the newly built-in coerce-into/coerce-from routines. Since this can be more complex than the existing single routine for type conversions, it might also contain an on-connect hook() allowing the conversion to send queries to the backend for configuration.

Having a hook would be nice so if the user overrides the default DateTime coercion, the ALTER SESSION also disappears.

[ancientwizard]

After sleeping on it I think it is best to have the default as simply return the string formatted just as returned by Oracle and not try any Fancy DateTime string swallows. The issue is DBIish is trying to anticipate/force what peeps should do but that just may not be possible for some consumers. I think it is best to leave it to the consumer to tell Oracle what to do with it self and stay out of the consumers way. So yes any helpers/hooks to that end to help :).

[ancientwizard]

I'm going to assume this rewrite you spoke of will take some time. I'm looking for an interim quick fix to provide ...

• the missing data types (two so far both different forms of timestamp); There could be others I didn't do an exhaustive search; I simple performed SELECT's on each table. I didn't try my views but figure they are all derived from tables so ...
• a mechanism to override the current behavior just so it returns the timestamps just as dished out by Oracle, a string, in the format the session told it to

I meant to mention in prior message

• I setup a Linux VM at home running an Oracle Instance just for this activity
• I have time how can I help?

It's not my first choice; but if required by my schedule (I need my app up and working ASAP) I'll clone DBIish and apply custom changes to get me over the hump while the larger rewrite is underway

[jonathanstowe]

If you're thinking of making changes make a pull request here, even if there are planned changes they may take longer than you had in mind. Unfortunately I haven't had the oracle drivers install since we first merged this.

[ancientwizard]

I've created the PULL request; I hope you find it okay-dokey; at the very least it communicates my intentions; By default it should still work as it did; however I augment the ALTER SESSION for a few missing types types and forced a TZ to help ensure times are right for all clients.

So I did finally find this nugget in the source

ALTER SESSION SET nls_timestamp_tz_format='YYYY-MM-DD"T"HH24:MI:SS.FFTZR'

Which is another reason it may fail; Consider this SQL> CREATE TABLE EXA_TZ 2 ( 3 ID INTEGER, 4 TS TIMESTAMP(0) WITH TIME ZONE 5 );

Table created.

SQL> using the formatter above causes DateTime to toss an exception; the timestamp includes no fractional seconds but still has the "."