Unhandled Exception: System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateTimeOffset'

[amul047]

Steps to replicate:

• Use nhibernate with npgsql
• Create an entity with a DateTimeOffset property
• Create a PostgresQL database table with timestamptz column
• Use appropriate column name mapping (we use FluentNHibernate for this)
• Try to get the entity in a nhibernate session

What happens:
Throws Unhandled Exception: System.InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateTimeOffset'

What should happen:
timestamptz column should map neatly to the DateTimeOffset property

Suggested solution:
Workaround from npgsql/npgsql#2641 (comment)

[fredericDelaporte]

It seems the PostgreSQL timestamp with time zone type (short timestamptz) is still not actually storing the offset, It just convert input values to UTC in the database and send them back in the time zone of the querying client, as I was telling in #1866.

PostgreSQL: no built-in support, sort of.
It handles two datetime types: timestamp and timestamp with time zone (short timestamptz). Both do not store an offset or a timezone but just a datetime. But timestamptz is always stored in UTC and yielded back to the client in local time zone. So we can store in UTC with timestamptz, but it will not be read back in UTC.

Here is the relevant section in latest PostgreSQL documentation:

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 (see Section 9.9.3).

So actually it looks very reasonable to me to treat timestamptz as just a (weird) DateTime, not a DateTimeOffset. There is no way you can preserve the offset with this type. Sure it still does represent the same time, but in UTC, yielded back as current client timezone. If you store the same time at different offset (like 10:00+00 and 12:00+02), they will be all yielded back at the same offset, the one of the client querying the database. If that is plus one, both values will read 11:00+01 at best. Then with .Net we add Npgsql in the mix, and I do not remember which kind of datetime it yields for this. (Utc? LocalTime? Unspecified? Correctly converted or not?)

The consequences of this is that even if we try to fake it as yielding a DateTimeOffset in spite of it not being able to actually store it completely, it will fails tests checking the offset is preserved, because it cannot store the original offset.

So I do not really see the point to try to work around this. You cannot actually store a DateTimeOffset as a timestamptz.

[fredericDelaporte]

My point of view seems to be the same than the one from Roji, who is Npgsql main contributor (or was, I am not following much Npgsql).
See here.

Reading up on Postgresql TIMESTAMPTZ type, @Emill seems to be right - the date is stored as UTC only without any offset - the only thing the type provides is conversion at insert-type. Since DateTimeOffset includes an offset, it is not possible for TIMESTAMPTZ to fully represent it. Follow this thread in the Postgresql mailing list, it's very relevant and suggests a composite type (i.e. two columns) to fully represent a timestamp along with its offset.

And in the issue you have linked, Roji repeats all this, and point to a documentation warning:

A common mistake is for users to think that the PostgreSQL timestamp with timezone type stores the timezone in the database. This is not the case: only the timestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset.