Timestamp issue?

MarkF · Post by **MarkF** » Fri 21 May 2010 02:46

It appears that there's a problem if a session's time_zone is changed in a script. Here's an example:

-- Note: actual timezone is -4:00.
ALTER SESSION SET TIME_ZONE='-07:00';
DROP TABLE table_tsltz;
CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
COMMIT;
SELECT c_id, c_tsltz, TO_CHAR(c_tsltz) FROM table_tsltz;

Will give the data:

Code: Select all

C_ID C_TSLTZ                      TO_CHAR(C_TSLTZ)             
---- ---------------------------- ---------------------------- 
1    01/01/2003 5:00:00.000000 AM 01-JAN-03 02.00.00.000000 AM 
2    01/01/2003 5:00:00.000000 AM 01-JAN-03 02.00.00.000000 AM 
3    01/01/2003 6:00:00.000000 AM 01-JAN-03 03.00.00.000000 AM

The second column is the ODAC version while the third column is the Oracle version of the data. It looks like ODAC is ignoring the session timezone information when converting the timestamp for display. Is there anything that can be done to fix that?

Thanks for any help.

-Mark

bork · Post by **bork** » Fri 21 May 2010 10:37

Hello

Thank you for the information. We have reproduced your issue. We will notify you as soon as we have any result.

bork · Post by **bork** » Fri 04 Jun 2010 14:14

Hello

The problem is fixed. This fix will be included in the next ODAC build.

MarkF · Post by **MarkF** » Fri 04 Jun 2010 15:56

Awesome! Thanks for taking care of that. Would it be possible to get a pre-release version or code fix so I can test it? I notice a new version just came out and I assume it will be a while before the next release. Thanks again!

-Mark

bork · Post by **bork** » Mon 07 Jun 2010 12:00

Hello

We can send you a pre-release version but we need to know your E-mail. Please provide us your E-mail here or contact us by E-mail (if you don't want to show it here).

MarkF · Post by **MarkF** » Mon 07 Jun 2010 16:18

Thanks! I've emailed support.

-Mark

MarkF · Post by **MarkF** » Thu 10 Jun 2010 14:37

I've tested the fix and it seems to work perfectly. Thanks for fixing it. Do you happen to know when the next release with this fix will be? Since this issue will cause incorrect results it might be a good idea to release the fix as soon as possible.

-Mark

bork · Post by **bork** » Fri 11 Jun 2010 11:22

The next build of ODAC will be released in 2-3 weeks. Till you can use the fixed source file that we've sent to you.