8

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; }
}
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
GBreen12
  • 1,832
  • 2
  • 20
  • 38
  • 1
    can you post your code? – IndieTech Solutions Mar 18 '15 at 17:49
  • Looks like PostgreSQL stores internally the UTC timestamp only. http://www.postgresql.org/docs/9.1/static/datatype-datetime.html – IndieTech Solutions Mar 18 '15 at 17:56
  • I could post code but I don't think it would be all that helpful. Basically I am just doing a Context.Files.Add(file) and then Context.Entry(file).Reload(). Error is thrown on the Reload(). I am using Reload because I have a serial field that I need to update. – GBreen12 Mar 18 '15 at 18:26
  • For `DateTimeOffset`, you should be using a `timestamp with time zone` type in postgres. But since you didn't post any code, all I can do is comment and vote to close. – Matt Johnson-Pint Mar 18 '15 at 19:53
  • Thanks for the edit, I removed my close vote. Can you please also tell us what data type the `CameraDate` field is within your postgres database? – Matt Johnson-Pint Mar 18 '15 at 20:04
  • I added the code... Like I said in a previous comment I didn't think it would be all that helpful. I am using the timestamp with time zone type which is why I am confused about it. I thought that it was supposed to take care of stuff like this. – GBreen12 Mar 18 '15 at 20:04
  • Oh sorry, I didn't see that part of your edit. :) I'll investigate deeper. – Matt Johnson-Pint Mar 18 '15 at 20:05
  • Question - What is the time zone of the computer where Entity Framework is running at? And what is the time zone of the Postgresql machine if it is different? Thanks – Matt Johnson-Pint Mar 18 '15 at 20:17
  • They are on the same machine, I'm just testing locally. It is in the Mountain Standard Time zone – GBreen12 Mar 18 '15 at 20:20

1 Answers1

3

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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • 3
    It looks like Pgsql's developer is currently suggesting against using `DateTimeOffset` and `timestamp with time zone`. See the discussion that begins with this [comment](https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/473#issuecomment-404881849) – Zoomzoom Jan 02 '19 at 15:00