Entity Framework and Oracle Timestamp with Time Zone

[pcrissman]

Hi,
We would like to use the Oracle data type TIMESTAMP WITH TIME ZONE. Entity Developer is mapping to .NET DateTime. When working with the data in this table via the Entity framework, there doesn't seem to be any awareness of the Time Zone that was set.

Does Devart & Entity Developer/Entity Framework, truly support the Oracle TIMESTAMP with TIME ZONE data type?

We are currently on version 5.25.44 for the Oracle Provider, and 2.20.38 for the Entity Developer.


Pam Crissman

[AndreyR]

Could you please describe the problem you've encountered?
What values have you obtained and what values were you supposing to obtain?
I have just made some tests with DataReaders and both Devart.Data.Oracle and
System.Data.OracleClient read/write to database values with time zone.

[acerbitdrain]

I think pcrissman is really asking why isn't Oracle TIMESTAMP WITH TIMEZONE mapped to .NET DateTimeOffSet?

I can only guess as I'm having the same issue with PostgreSQL and EF.

PgSqlDataReader returns DateTime for TIMESTAMP WITH TIMEZONE, I believe it should return DateTimeOffset.

Thoughts?

Regards,
Milos

[Shalex]

We will investigate this issue and notify you about the results as soon as possible.

[pcrissman]

I do something simple like the code below, and do not get what I would expect. In our test column I place various values in different timezones. The output shows the date stored but is timezone unaware.

I agree with a previous post, I was expecting a mapping to DateTimeOffset.

Can you provide immedate feedback as to whether there are plans to support this? and timing.. We are in the process of determining how to best handle timezones. If Devart is not supporting this, we may go in another direction.

Code: Select all

 Dim result = From rpc In EpicCtx.AaaRpcs _ 
                     Select rpc 
        For Each res In result 
            Dim aDate As DateTime = res.Test 
            Debug.Print(aDate.ToString & " - " & aDate.ToLocalTime.ToString) 
        Next 

[Shalex]

As for Oracle, we have implemented partial support for DateTimeOffset for the read-only mode in the latest builds - an entity with the DateTimeOffset property can be fetched from the database, but it cannot be inserted or modificated. For this, set the "DateTimeOffset" type for the corresponding properties in the conceptual model, and the "datetimeoffset" type should be set for the corresponding columns in the storage model. We plan to implement the full support for DateTimeOffset in the next build.

[Shalex]

We plan to release a new build next week.

[acerbitdrain]

Shalex, that is great, but will it also be a fix for PostgreSQL?

[Shalex]

Milos, unfortunately PostgreSQL doesn't have full implementation of the timestamp with time zone conception. The time in this column is stored in UTC. Current time zone set in timezone is added to the original UTC during the time transfer to the client (you can learn the current value of timezone by executing "show timezone", its value can be changed by "set timezone to 'TimeZoneName';").
Oracle stores information of actual timezone in the TIMESTAMP WITH TIME ZONE column, but PostgreSQL doesn't do this. There are distant plans to support this functionality in PostgreSQL (for example, refer to http://wiki.postgresql.org/wiki/Todo#Dates_and_Times). So it is not reasonable to work with this data type using DateTimeOffset at the moment.

[AndreyR]

The new build is available for download,

[acerbitdrain]

Thanks for your thoughtful answer Shalex.

I still believe that mapping TIMESTAMP WITH TIME ZONE to DateTimeOffSet is useful. It doesn't hurt to have the servers offset being returned along with DateTime. It is also very useful to be able save a DateTime with a different time zone.

Consider the following scenario:

My application and DB server is in +10 time zone and my client is in +9 time zone. My clients time zone is known to me from their preferences.
When I go to save a DateTime entered by that user I should be able to save it as something like

Code: Select all

DateTime userLocalTime = DateTime.Parse('User inputted date time');
DateTimeOffSet userDateTime = new DateTimeOffset(userLocalTime, new TimeSpan(9,0,0));
// Set my entities time
myEntity.CreationDate = userDateTime;

If I save this as a DateTime only, the server will assume its local time, +10, which is incorrect.

Thoughts?

[AndreyR]

Thank you for the suggestion, we will investigate the possibility of adding the mentioned functionality.