Devart Forums

Posted: **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.

Posted: **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

Posted: **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?

Posted: **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(); 
       }

Posted: **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!

Posted: **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.

Posted: **Thu 05 Apr 2012 16:52**

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

Posted: **Fri 06 Apr 2012 13:56**

Try using this code:

Code: Select all

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

Posted: **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 ?

Posted: **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.

Posted: **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 .

Devart Forums

Entity Framework and PostgreSQL timestamptz bug

Entity Framework and PostgreSQL timestamptz bug