Menu
I am having a problem with retrieving a DateTimeOffset from a PostgreSQL database using Entity Framework. As far as researching the problem, I found this article that helps me understand what the problem is, but I can't figure out how to fix it.
I have an API that allows users to upload files (mostly images) and it extracts the date that the image was taken and stores it in the database. It works great most of the time. However, if the date is between March 11 to sometime in April (date varies based on year) before 2007 it saves into the database fine but when trying to retrieve the row, it throws the error:
The UTC Offset of the local dateTime parameter does not match the offset argument.
I am using the Timestamp With Time Zone type in postgreSQL for the CameraDate field. I can't figure out how to get this to work correctly. If there is a way to do it without changing the database that would be preferable.
Examples:
2001-04-01 10:47:17-06 Works
2001-03-01 10:47:17-06 Works
2001-03-13 10:47:17-06 Doesn't work
2007-03-13 10:47:17-06 Works
Here is my code:
Context.Files.Add(file);
Context.Entry(file).Reload();
File class looks like this (Some fields removed for briefness):
public class File
{
[Column("FileId")]
public override Guid ID { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int DisplayId { get; set; }
public DateTimeOffset? CameraDate { get; set; }
}
876
First we create a table that has both timestamp and timestamptz columns using the below command: CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ); Then we will set the time zone of database server to Asia/Calcutta as below: SET timezone = 'Asia/Calcutta';
To store date values, you use the PostgreSQL DATE data type. PostgreSQL uses 4 bytes to store a date value. The lowest and highest values of the DATE data type are 4713 BC and 5874897 AD. When storing a date value, PostgreSQL uses the yyyy-mm-dd format e.g., 2000-12-31.
PostgreSQL timestamp example First, create a table that consists of both timestamp the timestamptz columns. Next, set the time zone of the database server to America/Los_Angeles . After that, query data from the timestamp and timestamptz columns. The query returns the same timestamp values as the inserted values.
PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9. The operations available on these data types are described in Section 9.9.
Ok, this appears to be a bug with DateTimeOffset in either npgsql or npgsql.EntityFramework. There appear to be several other issues logged in their issue tracker. In particular issue #542 is pretty close - but with a different exception message.
I suggest you create a new issue over there, as they are the best to help diagnose this further.
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
