I have a column in my table that is of data type 'timestamp with time zone' and I'm trying to map that to a .Net Type of 'DateTimeOffset'. However, when I try changing that in the Designer it states that I need a 'Type Converter' for this. When I click on the 'TypeConverter to use' column in the Field mappings section it shows <None>.

I'm using Designer Version 5.5 (5.5.0) RTM. Build Version 02-Nov-2018. And Npgsql version 4.0.4.

Can you please point me in the right direction?

Thanks so much!

That's the default .NET Type returned by the NpgSql.

ref: https://www.npgsql.org/doc/types/datetime.html and: https://www.npgsql.org/doc/types/basic.html

Time with Timezone (timetz) has as default DateTimeOffset. Timestamp with timezone (Timestamptz) has 'DateTime' as default type. We map only default types so we map Timezone with Timestamp to DateTime as that's the default type for Timezone with timestamp.

DateTimeOffset however is a different kind of type, it represents a point in time relative to UTC. Because of this you need to convert the DateTime value that is returned by Npgsql into a DateTimeOffset (we only read the default GetValue() value, we don't read the GetFieldValue<T>() value which returns the alternative type in Npgsql).

This conversion between DateTime and DateTimeOffset has to be done in a type converter, and we don't ship that particular one with the framework so you have to create one yourself. This is documented here: https://www.llblgen.com/Documentation/5.5/SDK/gui_implementingtypeconverter.htm

As the whole time / timezone aspect of postgresql & Npgsql is rather confusing at times, just to be clear: you really want to convert it to DateTimeOffset?

So the reasoning behind wanting to use DateTimeOffset is that we are sending our dates/times via Web API to Angular applications. The time parts were always getting skewed when they were sent between the two (based off the timezone offset). To alleviate this, we started just sending the Unix Time in Milliseconds value as that is easily converted in Angular to the correct value we needed and easily went back into a DateTimeOffset object in our API.

Most of our databases are MS SQL, which has a DateTimeOffset datatype. We are moving some of our databases to PostgreSQL and needed a way to store the same data types. Timestamp with time zone seemed like the best fit, however it returns DateTime objects already converted to Local time. This can be a little confusing as it is stored as UTC in a column suffixed with Utc and then it comes back and is already converted to local time.

Maybe you need to use timetz (in Postgres) instead.

Alright, I'll try just converting the DateTime object to a DateTimeOffest. If that doesn't do what I need, I'll rework some of my logic.

Thanks!