TIMESTAMP WITH TIMEZONE daylight savings bug

mas · Post by **mas** » Tue 29 Mar 2011 09:00

I have an application that records 2 timestamps in a table, both columns are of type TIMESTAMP WITH TIMEZONE.

The first column, APP_RECEIVE_TIME, is set by our .NET application.
The second column, DB_INSERT_TIME, is set by our database via the column's default value.

After the daylight savings time switch-over, I found that the APP_RECEIVE_TIME was incorrect. It contained the local time with the wrong offset value. The expected offset value was +02:00.

Example:
APP_RECEIVE_TIME: 28-MAR-11 05.03.53.226108 PM +01:00
DB_INSERT_TIME: 28-MAR-11 05.03.54.277826 PM +02:00

I would like to note that I am using Direct=true, because I have previously run into a bug with TIMESTAMP WITH TIMEZONE and Direct=true (http://www.devart.com/forums/viewtopic.php?t=19911).

This problem has been observed in dotConnect for Oracle 6.10.96.0.

Edit

We tested with Direct=false (with Oracle Client), and the offset is set correctly. The problem only occurs with Direct=true.

Shalex · Post by **Shalex** » Wed 30 Mar 2011 13:32

We have reproduced the daylight savings time problem in the Direct mode using dotConnect for Oracle v 6.10.126.

Code: Select all

//  create table tstable(
//    APP_RECEIVE_TIME TIMESTAMP WITH TIME ZONE,
//    DB_INSERT_TIME TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
//  );

    using (OracleConnection conn = new OracleConnection()) {
        conn.ConnectionString = "Direct=true;***";
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandText = "insert into tstable(APP_RECEIVE_TIME) values (:p1)";
        cmd.Parameters.Add("p1", OracleDbType.TimeStampTZ).Value = new OracleTimeStamp(DateTime.Now, TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now));
        cmd.ExecuteNonQuery();
    }

Output -
а) OCI (Ok):
APP_RECEIVE_TIME - 30-MAR-11 16:05:26,938769000 +02:00
DB_INSERT_TIME - 30-MAR-11 16:05:26,949000000 +02:00
б) Direct (wrong):
APP_RECEIVE_TIME - 30-MAR-11 16:03:36,896766000 +02:00
DB_INSERT_TIME - 30-MAR-11 15:03:36,905000000 +01:00

I will notify you when the issue is fixed.

vladi · Post by **vladi** » Tue 05 Apr 2011 11:25

Hello,

we need the bug fix ASAP! This is a show stopper in our project now.

When do you plan to publish the new dotConnect release with the bug fix?

Thank you.

Shalex · Post by **Shalex** » Wed 06 Apr 2011 11:20

We have fixed the bug with TIMESTAMP WITH TIMEZONE in the Direct mode when daylight saving time. I will post here when the corresponding build is available for download. We plan to release the new build this week.

Shalex · Post by **Shalex** » Thu 07 Apr 2011 14:24

New build of dotConnect for Oracle 6.10.135 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=20708 . This build includes the fix for the bug with TIMESTAMP WITH TIMEZONE in the Direct mode when daylight saving time.