Entity Framework and PostgreSQL timestamptz bug

[Boone]

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]

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]

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]

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]

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]

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]

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

[Shalex]

Try using this code:

Code: Select all

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

[muthu]

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]

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]

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 .