Forum Stats

  • 3,855,338 Users
  • 2,264,499 Discussions
  • 7,905,972 Comments

Discussions

TIMESTAMP WITH TZ region information is lost when used in UDT

MaikR
MaikR Member Posts: 42 Bronze Badge
edited Dec 4, 2015 3:33AM in ODP.NET

Hello,

Actually we change some stored procs for better performance.Our procedures are called from C# 4.5 with Ado.Net unmanaged Oracle.DataAccess.dll version 4.121.2.0. Database is 11.2.0.3. EE.

Because our system is time critical with different locals, we set the session timezone to region 'Europe/Berlin'.

Our old procedures have some parameters with type TIMESTAMP WITH TIME  ZONE. We set in C# the parameter to datetime.now, and in stored proc we save the value to a table column of type TIMESTAMP WITH TIME ZONE. If i read the value from column, i get a value like '04.12.15 09:18:34,041000 EUROPE/BERLIN', which is correct!

Now we created new UDT as follows:

create type tz as object (time timestamp with time zone);

create type tz_tbl as table of tz;

And in C# we use and fill the appropriate object with some datetime values. Our stored proc runs fine, take all parameter timestamps and save them in bulk operation to our table. But if i select the column now, i get only the timestamps with timezone offset information! The timestamp region information is lost! And in this case, oracle cannot compute date differences between summertime and winter time correctly!

I don't know, if this is a bug, but i think, that this is very inconsistent!

What is your experience with that?

Thanks for your help!

Maik

Tagged:
This discussion has been closed.