Support for DateTimeOffset for PostgreSQL provider

We have introduced DateTimeOffset support for PostgreSQL provider in 5.0.11 RC. Here, we want to explain some restrictions of this RDBMS which affect our implementation of that type support.

We warn you that support for date/time with time zone is different from Oracle Database and Microsoft SQL Server implementations. The last two RDBMS has full support for all parts of date/time including time zone, but PostgreSQL doesn't.

If you want to use DateTimeOffset type in domain models on PostgreSQL storage we need to explain some things you may face with.

PostgreSQL has two types which can store both date and time - timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). You may glance at them and think that everything is OK and you can store date/time with time zone in the last one. BUT, if you look carefully you will notice that values of both timestamp and timestamptz types have same size - 8 bytes. Strange, right?

Here is the quote from PostgreSQL documentation:

"All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client."

That means PostgreSQL does not store time zone part of given value at all. It will return you results in the time zone which was specified in PostgreSQL configuration file or the zone which was set manually for certain connection.

For instance, RDBMS instance was configured for time zone  UTC+07. You insert value '2016-09-10 18:19:22.000 +02:00' to some timestamptz column and query it back. The storage will return you '2016-09-10 23:19:22.000 +07:00' and it will do it all the time until you change server time zone.

Such odd implementation of timestamptz influences the results of queries you will receive using DataObjects.Net.

It will influence on date and time parts extraction. You will receive them for a timestamp value in server time zone. It is really important for such parts as Day, Hour, Minute, TimeOfDate and others which may be changed by switching time zone. Also you will get same offset for all DateTimeOffset values.

You also cannot use in LINQ queries methods like

- DateTimeOffset.ToOffset(TimeStamp offset);
- DateTimeOffset.ToLocalTime();
- DateTimeOffset.ToUniversalTime().

The reason is in another PostgreSQL restriction. It provides you a functionality of switching time zone for timestamp values by using  AT TIME ZONE statement or its equivalent - function  timezone(zone, timestamp). This table describes variants of  how it can be used. Note that, if you move a timestamptz value to certain time zone you will get a value without time zone . It makes it impossible to implement support for the methods I mentioned.

However, such type members like DateTimeOffset.LocalDateTime and DateTimeOffset.UtcDateTime will work, because they return values of DateTime type and such operations can be performed by AT TIME ZONE statement and these members are usable in queries.


These restrictions and particularities should be taken into account if you decide to use DateTimeOffset type in a domain model built on PostgreSQL storage. We would recommend you to set time zone in postgresql.config file or with SET statement for connection. It will prevent you from getting values with unexpected offsets. Also you will have to perform switching to time zones you need on client side.

We hope this text will help you avoid mistakes.