roji
commented
8 years ago From @kmschaefer on May 12, 2015 20:22
I made some changes in Npgsql.EntityFramework to match Npgsql, and the MVC-generated web page worked:
Index
Create New
datefield timestampfield timestamptzfield 4/27/2015 12:00:00 AM 4/27/2015 9:25:16 PM 4/28/2015 1:25:16 AM Edit | Details | Delete
NpgsqlProviderManifest.Manifest.xml:
Added: Type Name="timestamptz" PrimitiveTypeKind="DateTime" FacetDescriptions Precision DefaultValue="0" Constant="true" FacetDescriptions Type
Commented out: Type Name="timestamptz" PrimitiveTypeKind="DateTimeOffset" FacetDescriptions Precision Minimum="0" Maximum="10" DefaultValue="7" Constant="false" FacetDescriptions Type
NpgsqlProviderManifest.cs:
case "date": case "timestamptz": return TypeUsage.CreateDateTimeTypeUsage(primitiveType, 0); //case "timestamptz": // if (storeType.Facets.TryGetValue(PrecisionFacet, false, out facet) && // !facet.IsUnbounded && facet.Value != null) // { // return TypeUsage.CreateDateTimeOffsetTypeUsage(primitiveType, (byte)facet.Value); // } // else // { // return TypeUsage.CreateDateTimeOffsetTypeUsage(primitiveType, null); // }
I would prefer to make changes to Npgsql rather than Npgsql.EntityFramework, because I think we could end up messing up the timestamptz data in the database.
From @kmschaefer on May 12, 2015 19:33
This is the error I receive when trying to view the data in an MVC-generated web page: The 'timestamptzfield' property on 'test' could not be set to a 'System.DateTime' value. You must set this property to a non-null value of type 'System.DateTimeOffset'.
Here is a comparison showing how the fields and data look via psql, referencing Npgsql only, and referencing Npgsql and Npgsql.EntityFramework.
psql on Linux server:
=> \d test Table "test" Column | Type | Modifiers ------------------+-----------------------------+----------- id | integer | not null datefield | date | timestampfield | timestamp without time zone | timestamptzfield | timestamp with time zone | Indexes: "test_pkey" PRIMARY KEY, btree (id)
=> insert into test (id, datefield, timestampfield, timestamptzfield) values (1, '2015-04-27 21:25:16.102666-04', '2015-04-27 21:25:16.102666-04', '2015-04-27 21:25:16.102666-04');
=> select * from test; id | datefield | timestampfield | timestamptzfield
----+------------+----------------------------+------------------------------- 1 | 2015-04-27 | 2015-04-27 21:25:16.102666 | 2015-04-27 21:25:16.102666-04
Console Application Referencing Npgsql Only:
Columns: id (System.Int32) datefield (System.DateTime) timestampfield (System.DateTime) timestamptzfield (System.DateTime) // Should be System.DateTimeOffSet
Data: id: 1 datefield: 4/27/2015 12:00:00 AM timestampfield: 4/27/2015 9:25:16 PM timestamptzfield: 4/28/2015 1:25:16 AM
Referencing Npgsql and Npgsql.EntityFramework (EF 6.1.3)
public partial class test { public int id { get; set; } public Nullable datefield { get; set; }
public Nullable timestampfield { get; set; }
public Nullable timestamptzfield { get; set; } // Created correctly by the ADO.NET Entity Data Model wizard
}
Viewing the MVC-generated web page gives the error shown above. I found this issue about DateTime vs DateTimeOffset: https://github.com/npgsql/npgsql/issues/347. From what I am seeing, it appears Npgsql needs to be changed back to convert timestamptz to DateTimeOffset.
Copied from original issue: npgsql/npgsql#604