Entity Framework and Oracle Timestamp with Time Zone

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
pcrissman
Posts: 8
Joined: Mon 28 Sep 2009 13:16

Entity Framework and Oracle Timestamp with Time Zone

Post by pcrissman » Mon 02 Nov 2009 16:41

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 03 Nov 2009 13:25

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
Posts: 10
Joined: Sat 07 Feb 2009 10:09

Post by acerbitdrain » Wed 04 Nov 2009 08:33

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 05 Nov 2009 08:42

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

pcrissman
Posts: 8
Joined: Mon 28 Sep 2009 13:16

TimeZone issue

Post by pcrissman » Thu 05 Nov 2009 13:45

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 05 Nov 2009 16:20

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 06 Nov 2009 10:31

We plan to release a new build next week.

acerbitdrain
Posts: 10
Joined: Sat 07 Feb 2009 10:09

Post by acerbitdrain » Fri 06 Nov 2009 13:02

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

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 10 Nov 2009 10:25

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 20 Nov 2009 15:49

The new build is available for download,

acerbitdrain
Posts: 10
Joined: Sat 07 Feb 2009 10:09

Post by acerbitdrain » Sat 21 Nov 2009 12:30

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 24 Nov 2009 14:09

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

Post Reply