I have data stored in an oracle TIMESTAMP WITH TIME ZONE column, and I am now trying to read it back into a C# DateTimeOffset variable using Dapper. The issue is that dapper ignores the offset value in the database, and always populates my variable with the environment's current offset.
Is there a simple way to get dapper to recognize the offset value from the database?
Basically I want to see something along these lines work:
var input=new DateTimeOffset(2016, 3, 15, 14, 30, 0, TimeSpan.Zero);
DateTimeOffset output;
using(var connection=new OracleConnection(QueryConnectionString)) {
output=connection.ExecuteScalar<DateTimeOffset>("Select to_timestamp_tz('"+input.ToString("yyyy-MM-dd HH:mm zzz")+"', 'YYYY-MM-DD HH24:MI TZH:TZM') From DUAL");
}
Assert.AreEqual(input, output);
As written this gives an invalid cast exception, it appears that dapper reads it as a DateTime and then tries to cast it to DateTimeOffset, ignoring the offset value.
My code that queries a table and populates a class object defined with these types does not throw an error, but it populates the object instance with the local offset rather than the value in the database. So I would end up with 2016-03-15 14:30 -5 instead of 2016-03-15 14:30 +0, if I were working with the above input value.