Entity Framework and PostgreSQL timestamptz bug

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Boone
Posts: 2
Joined: Tue 10 May 2011 19:06

Entity Framework and PostgreSQL timestamptz bug

Post by Boone » Tue 10 May 2011 19:19

We are using Entity Framework with dotConnect PostgreSQL but have run stuck when working with columns of type "timestamp with timezone."

When querying the data, the timezone offset is lost. The DateTime object's Kind parameter is set to "DateTimeKind.Unspecified" and the time is simply set in whatever timezone the server happened to return.

I believe this is a bug. When querying timestamptz columns, DateTimeKind should be set to either Local or Utc with the date converted as necessary from whatever timezone was returned by the server.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 12 May 2011 13:11

Unlike Oracle Timestamp of time zone data type, PostgreSQL does not store the actual time zone.
The PostgreSQL documentation states:
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct

Boone
Posts: 2
Joined: Tue 10 May 2011 19:06

Post by Boone » Thu 12 May 2011 21:39

The actual details of how PostgreSQL stores data is not the point, but the fact that a timestamp WITH a timezone was returned by the server, but the timezone was silently dropped by the client. Now you are no longer able to determine the true time in UTC with certainty.

In hindsight, using a simple timestamp column which stores straight UTC would have avoided these issues.

Assuming we aren't able to guarantee the server timezone, what is the most reliable way to handle this? Is there a good way to change the timezone setting for a session w/ Entity Framework?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 13 May 2011 12:53

Try something like

Code: Select all

context.ExecuteStoreQuery("SET TIME ZONE '-05'")
If you are using Entity Framework v1, then there is no ExecuteStoreQuery method, and you will need to use the following code in the OnContextCreated method:

Code: Select all

      DbConnection conn = (Connection as EntityConnection).StoreConnection; 
       if(conn.GetType().Name == "PgSqlConnection") { 
         var cmd = conn.CreateCommand("SET TIME ZONE '-05'");
         conn.Open(); 
         cmd.ExecuteNonQuery(); 
       }

Pankon
Posts: 1
Joined: Tue 10 Jan 2012 10:54

Post by Pankon » Tue 10 Jan 2012 14:46

AndreyR wrote:Try something like

Code: Select all

context.ExecuteStoreQuery("SET TIME ZONE '-05'")
Has anyone tried it?
Does it really work?

It does not work for me!

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

Post by Shalex » Mon 16 Jan 2012 14:09

This setting is active only for the current session (http://www.postgresql.org/docs/8.3/static/sql-set.html). So please try:

Code: Select all

context.Connection.Open();
context.ExecuteStoreQuery("SET TIME ZONE '-05'");
Explicit connection opening guarantees that the connection will not be closed/opened in each interoperation with database.

muthu
Posts: 10
Joined: Tue 28 Sep 2010 19:31

Post by muthu » Thu 05 Apr 2012 16:52

is there a way for me to force entity framework to return System.DateTime universal(UTC)

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

Post by Shalex » Fri 06 Apr 2012 13:56

Try using this code:

Code: Select all

context.Connection.Open();
context.ExecuteStoreQuery("SET TIME ZONE '00'");

muthu
Posts: 10
Joined: Tue 28 Sep 2010 19:31

Post by muthu » Mon 09 Apr 2012 14:44

My postgresql database server is always set to timezone "UTC". How do I force devart Entity Framework to return System.DateTime with System.DateTimeKind enum set to UTC but not Unspecified ?

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

Post by Shalex » Wed 11 Apr 2012 14:37

The PgSqlDataReader.GetDateTime method (which is used by our provider within Entity Framework) always returns DateTime with DateTimeKind.Local. We will correct the behaviour: the Kind property of the returned DateTime object will be initialized with DateTimeKind.Local.

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

Post by Shalex » Thu 26 Apr 2012 14:52

New build of dotConnect for PostgreSQL 5.80.350 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23998 .

Post Reply