Displayed Date Discrepancies

[astouder]

Hi,

I have a legacy system that we're trying to migrate up to Oracle 19. It works with an old version of Devart, 5.0.536 against 11g and 12c Oracle databases. The application is in a verified environment so cannot be frequently updated, which is why the DLLs are so old. As Windows 7 is going out of service, we're attempting to migrate to Windows 10, and hopefully Oracle 19 in the process.

The tests were run on Windows 10 + Oracle 12c with the old DLLs as a sanity check and things were working well with that combination. When we updated to Oracle 19, we could not connect to the database. To resolve this, we purchased the latest version of Devart dotConnect for Oracle and after installation were able to connect to the Oracle 19 database. However, when we began running the automated test cases, we noticed that the dates displayed by the application appear to have been shifted by the time zone an extra time. The dates stored in the database correctly return in the expected local time from application like sqlplus and Golden6, so they're getting in to the database correctly.

For example, a date time in the database of "7/8/2020 11:09:02.368699 AM" (American dates, pulled with US/Eastern timezone) is appearing in our application as "7/8/2020 7:09:02.368699 AM".

The application code has not changed, only the installation of the Devart DLLs. The database date time shown (the first one) is from running a query using Golden6 or sqlplus to connect, and correctly corresponds to the local time when I performed logging actions.

I see similar behavior using a 12c database, though it's only off by -3 hours (showing 9:04 and 6:04, database and application respectively). I imagine this is due to differences in the versions of the timezone files on each database server (the 12c DB is at 18, while the 19 DB is at version 32), which I do not have access to change.

The database uses TIMESTAMP WITH LOCAL TIME ZONE to store date time entries. When the application connects, it alters the session to set the current timezone by running the following (US/Eastern used here, but it's variable depending on the user's selection, I've also removed the schema name as it's specific to our application):

Code: Select all

alter session set NLS_SORT='BINARY_CI' current_schema = <schema_name> time_zone='US/Eastern' constraints=deferred

Is this expected behavior with later versions of Devart? Or is there a way to prevent this additional time shifting?

Please let me know if you need any additional information, thanks!

[Shalex]

1. It's not clear how you read date time values from the database. Please localize the issue in a small test project and send it with the corresponding DDL/DML script via our contact form.

2. Also, specify the following information in your email:
a) your connection string (mark confidential information with asterisks)
b) time_zone of your Oracle 19 database
c) time zone of Windows where you run the code

[astouder]

Sorry for the delay getting back to you. While I was working on creating a small application to duplicate this issue, we were able to find a fix for a different problem that allows us to connect to Oracle 19 without the updated DLLs, so we don't see the time shifting issue anymore.

Thank you anyway.

[astouder]

Hi,

We ended up finding another issue with the solution we thought we had for connecting to Oracle 19c. We submitted a test project for the issue above to the link provided this past Monday, as requested, but have not gotten a response. It was submitted on my supervisor's account, as the license was purchased for me through their account.

Thank you.

[astouder]

I've been attempting to contact the service group by email over this issue, including sending the location in the code that is causing the issue, but have not heard back in several days.

After debugging with support, the issue appears to be as follows:

• When using Direct=true and Timestamp with Local Time Zone in Oracle, the latest Devart DLLs will double-shift the timezone when data is displayed back to the user. So, something that occurs in a -4 timezone at 10am will appear as if it was at 6am. However, the database entry (when connecting to the database with sqlplus/golden and the same -4 timezone) is correct. This happens when using sysdate or Current_Date for entering the timestamp into the database.

• When Direct=false is used, this does not occur and the dates report back correctly. (Direct=false is not an option for us)

I sent a test application that illustrates this issue to the support team, and ran the items that I was requested to run. I did forget to run one item on one of the sequences on the first run, but later sent that back.

While Direct=false works correctly in our test application, we are working with a legacy code base, and we cannot change the code to use this. We are able to install new dependencies instead.

To investigate, I built the DLLs using the source release that came with our license and hooked up my test application. When I remove the lines numbered 719-720 in Devart.Data.Oracle.OracleTimeStamp, the issue goes away. This change does not appear to affect Direct=false connections either. That said, I realize this does not take all situations/use cases into account, but this is the code that is causing the double shift in this particular situation. We are not allowed to use custom builds of vendor DLLs in our production applications.

We have not gotten a response from the dev team in 3 days since I emailed about the bug location in the source code. Before, that I had not received a response to my previous email in 10 days (this was the time that I realized I forgot a screenshot and sent back the missing one after checking in with the team again and being told it was missing, I apologize for that mistake).

The ticket number we were given is 314489.

I don't like to be pushy, but we're running out of time in our project timeline. This is for a project at a large corporation working in a verified space. We will need to document and adjust our processes with approved workarounds to deal with this issue if it is not fixed, which is expensive.

If this were to be fixed later, deploying a bug fix after our current project timeline would likely cost $30,000 to test, deploy, and validate the change. If there's any way I can get a response on whether or not this bug will be fixed, and what that timeline looks like, I would greatly appreciate it.

[Shalex]

Sorry for the delayed response. Your ticket is in progress and we are working on it. Unfortunately, we will need more time to resolve the issue. Our tests on a different timezone is successful, we need to create an environment that is similar to yours. At the same time, our developers will review the code you pointed to. We will do our best to send a reply as soon as possible.

[Shalex]

We have reproduced different results in OCI and Direct modes and are investigating the issue.

[Shalex]

dotConnect for Oracle v9.13.1098 includes improvements:
* The bug with double shifting timezone when retrieving the TIMESTAMP WITH LOCAL TIME ZONE values in the Direct mode is fixed
* The bug with detecting daylight saving time settings on a current workstation when reading the TIMESTAMP WITH LOCAL TIME ZONE values in the Direct mode is fixed

Refer to viewtopic.php?f=1&t=42246.