Is there a region support for timestamp columns?

[holger_nis]

Lets execute the following statments to create a test case

Code: Select all

ALTER SESSION SET TIME_ZONE='Europe/Berlin';

CREATE TABLE TEST_TIME (
  ID NUMBER (28,0) NOT NULL,
  TSZ TIMESTAMP(6) WITH TIME ZONE,
  CONSTRAINT TEST_TIME_PK PRIMARY KEY (ID ));

INSERT INTO TEST_TIME ( ID, TSZ) VALUES ( 1, sysdate);
/

If we query the data using

Code: Select all

select TSZ, TO_CHAR(TSZ, 'TZR') TSZ_ZONE from TEST_TIME where ID = 1

it gives a result like this

Code: Select all

28.09.2017 13:43:11,000000 +02:00	EUROPE/BERLIN

Now we create a Delphi application that contains a TOraSession, TSmartQuery and a grid component.
After connect, we execute

Code: Select all

ALTER SESSION SET TIME_ZONE='Europe/Berlin';

Then we open the TSmartquery, and change the hour of the timestamp.

Now we query again

Code: Select all

select TSZ, TO_CHAR(TSZ, 'TZR') TSZ_ZONE from TEST_TIME where ID = 1

it gives a result like this

Code: Select all

28.09.2017 13:43:11,000000 +02:00	+02:00

That means, the region information has been lost.

Problem 2:

If we use a TIMESTAMP with local timezone column, and update to dates in January and in July, it should automatically apply a +01:00 for winter time and +02:00 for summer time.
It works fine, when you are using SQL. When you are changing the data in the grid, there is no difference between summer and winter time.

Problem 3:

In 64 bit mode, when connected to an Oracle 11g2 database, we often get an ORA-01805 "possible error in date/time operation", when trying to change data using a data grid. (Text edit, no date/time picker)

[MaximG]

Problem 1 and 2 : we started testing ODAC behavior according to your description and will inform you about the results shortly
Problem 3 : Please specify - DB server and the client with the installed Oracle client software x64 is located in one time zone ? Is the described error reproduced when using Oracle client software x32 ?

[holger_nis]

Server:

• Oracle 11.2.0.3.0 Enterprise Edition
  AMERICAN, AMERICA, WE8MSWIN1252

Client

• Windows 7
  12.1.0.0.0 Production
  AMERICAN_AMERICA.WE8MSWIN1252

Client and Server are both in same time zone (Berlin)
Problem does only occur in 64 Bit, 32 Bit is OK.

I tried it again with 11.2.0.1.0 (x64) client and it works OK.
So it looks like it is related to a problem with Oracle 12 client and 11.2 database.

[holger_nis]

Are there any news regarding this issue?

[MaximG]

We continue the work on fixing the described error and hope to get fast results, which we will let you know at once