Timestamptz translation and session timezone

[yallie]

Sorry for the dumb question, perhaps I am missing something obvious. According to the PostgreSQL documentation, timestamp with time zone values are returned using the current session's time zone, i.e.:

> set timezone='utc';
> select now();
 2017-03-02 19:06:47.919652+00

> set timezone='+3';
> select now();
 2017-03-02 22:06:56.519027+03

But I can't get it to work with Npgsql. Looks like the driver uses system's time zone settings instead of the supplied timezone value.

Steps to reproduce

Here is my code:

var conn = new NpgsqlConnection(connString);
conn.Open();

Console.WriteLine("============ Setting timezone to UTC ==============");
var cmd = conn.CreateCommand();
cmd.CommandText = "select set_config('timezone', 'UTC', true)"; // or "set timezone to 'UTC'";
cmd.ExecuteNonQuery();

cmd.CommandText = "select current_timestamp";
using (var reader = cmd.ExecuteReader())
{
	while (reader.Read())
	{
		Console.WriteLine("Now: {0}", reader.GetFieldValue<DateTime>(0));
	}
}

Console.WriteLine("============ Setting timezone to +5 ==============");
cmd.CommandText = "select set_config('timezone', '+5:00', true)"; // or "set timezone to '+5:00'";
cmd.ExecuteNonQuery();

cmd.CommandText = "select current_timestamp";
using (var reader = cmd.ExecuteReader())
{
	while (reader.Read())
	{
		Console.WriteLine("Now: {0}", reader.GetFieldValue<DateTime>(0));
	}
}

The output:

============ Setting timezone to UTC ==============
Now: 02.03.2017 22:18:55
============ Setting timezone to +5 ==============
Now: 02.03.2017 22:18:55

The issue

I'm expecting that the first time value is returned in UTC timezone, and the second time in UTC+5 timezone. Instead, it's always returned as UTC+3 which is my system's timezone. I've tried different versions of the driver, and it works the same.

Further technical details

Npgsql version: 2.2.5 and 3.2.1
PostgreSQL version: 9.6.1
Operating system: Windows 10

[roji]

This is the (current) expected behavior, even if it's confusing. tl;dr timestamptz are converted to the local timezone on the client (Npgsql), not on the server (PostgreSQL).

First, keep in mind that the timestamptz type doesn't store a timezone - it's just a timestamp. The only difference between timestamp and timestamptz is that PostgreSQL will assume that timestamptz values are UTC and will convert them to the configured timezone (in the PostgreSQL session) when outputting as text (as you've seen).

When Npgsql reads values from PostgreSQL, those values are in binary encoding. PostgreSQL's binary encoding of timestamptz is simply the UTC timestamp value, as stored in the column - there's no timezone to transfer. To (partially) mimic the PostgreSQL conversion behavior, when reading a timestamptz value, Npgsql will convert it to the local timezone. However, this means the client machine's timezone (where Npgsql is executing), and not PostgreSQL's timezone - this is where the discrepancy is coming from.

To truly match PostgreSQL's behavior, Npgsql would have to be aware of the PostgreSQL session timezone. This is theoretically possible, but involves converting PostgreSQL timezone identifiers to .NET ones - not at all trivial, and probably not worth the bother, although if someone wants to take a look go ahead.

Closing as this is the expected behavior, but feel free to continue the conversation and ask questions.

[yallie]

@roji, thanks for the reply!

First, keep in mind that the timestamptz type doesn't store a timezone - it's just a timestamp.

Yes, I'm aware of that. Timestamptz is indeed a timestamp at utc, an inaccurate naming.

To truly match PostgreSQL's behavior, Npgsql would have to be aware of the PostgreSQL session timezone.

Exactly, that's my point. Is that information (the session timezone) available to Npgsql when, say, data reader is processed?

In my scenario (ported from Oracle), we initialize the database session after opening the connection by calling a stored procedure: init_session(user_id, lang_id, timezone, etc). The procedure prepares the database context for the user: views are translated to the user's language, timestamps converted to the user's timezone, etc. Oracle driver handles the DateTime translation just fine: the user saves his local DateTime.Now and gets it back just the same.

This is theoretically possible, but involves converting PostgreSQL timezone identifiers to .NET ones - not at all trivial

We use timezone='+1:00' syntax both in Oracle and PostgreSQL. These numeric offsets seems to be easy to support. So, is that current session timezone setting always readily available to Npgsql or it should be queried via extra network roundtrip? We don't use Npgsql to change the session's timezone explicitly — it is done inside the init_session procedure, among other things.

[yallie]

To (partially) mimic the PostgreSQL conversion behavior, when reading a timestamptz value, Npgsql will convert it to the local timezone.

I think, the real problem is that DateTime.ToLocalTime() uses the OS's time zone information. It cannot be overridden per-thread or even per-application, it's system-wide. So if Npgsql had a thread-local setting to override the TimeZoneInfo used for the timestamp conversion, that would just fine. Is that possible?

[roji]

@yallie, it's very easy to work around this (i.e. by explicitly doing timezone conversion client-side). It's true that DateTime.ToLocalTime() always converts to the machine's local time, but on the other hand you can simply call .Add(TimeSpan) to apply the timezone you want. Another option is to read a DateTimeOffset from PostgreSQL (which for timestamptz will have timezone UTC) and then again, apply your desired timespan.

Regarding changing Npgsql to make it convert timestamptz based on the PostgreSQL-configured timezone... It's trivial to make Npgsql aware of the session's time zone. PostgreSQL sends protocol messages to let clients know of any changes to a list of parameters, and timezone is one of them - see the docs. In other words, Npgsql could pick up these changes and update a field on the connection, which would represent "the session's current timezone".

The real problem, as I said before, is that PostgreSQL uses IANA time zone names (e.g. America/New_York), while .NET TimeZoneInfo uses Windows time zone names (e.g. Eastern Standard Time). This means that converting PostgreSQL's timezone names to something usable by Npgsql is non-trivial - see this question. In a nutshell Npgsql would have to internally maintain a mapping, which may need to be updated from time to time... Not something I'd like to do unless it's necessary, but this issue simply doesn't seem important enough to go down that road...

[yallie]

it's very easy to work around this (i.e. by explicitly doing timezone conversion client-side).

Yes, that may be fine if the application is PostgreSQL-only. But it's a pain for multi-database applications, in my case Oracle/Postgres. It's OK to have different queries for different databases, but it's too bad if I'm also forced to process the results differently throughout the application code.

The real problem, as I said before, is that PostgreSQL uses IANA time zone names (e.g. America/New_York), while .NET TimeZoneInfo uses Windows time zone names (e.g. Eastern Standard Time).

Yes, I understand that it's not an easy task, and I agree that it's quite a burden to support (I guess it's easier for the Oracle driver because everything is handled by the database itself). Perhaps it's OK to leave this timezone translation complexity to the end-user, but I don't think it's fine to leak it down to the application code... What about providing an extension point to Npgsql so I can translate dates according to my needs?

To (partially) mimic the PostgreSQL conversion behavior, when reading a timestamptz value, Npgsql will convert it to the local timezone. [...]
this issue simply doesn't seem important enough to go down that road...

What about the multi-tier applications? Application server connects to the database to process the client requests. App-server threads belong to different user sessions and have different time zones. So it's just pointless to convert timestamps using the app-server's time zone info.

TL/DR: please provide an extension point to Npgsql so I can handle all datetime translation by myself. It can be as easy as an interface with two methods (convert DateTime before sending to PostgreSQL + convert it back upon reading). The default implementation is just the current behavior, namely DateTime.ToUTC() and DateTime.ToLocalTime(). And my implementation is up to me. Would that be OK?

Thanks, Alex.

[roji]

Sorry for not answering sooner on this. One of the main new features of 3.3 will probably be general extensibility for type converters, allowing you to add type handlers for types Npgsql doesn't support, or replace standard type handlers with custom ones - this would allow you to do any sort of timezone conversion you want. See #1475.

[Emill]

One thing you can also do is convert the time stamp to a string on the server (by appending ::text). Then you will get the expected value if all you want is just to print it anyway to the user. Or convert it to a "timestamp without time zone". Then the returned timestamp will be a DateTime object with the correct time zone.

[yallie]

Sorry for not answering sooner on this.

No worries @roji! Thanks for your answer. We decided to disable the support for the time zones in the application completely until we find a way to work around the time translation issue.

One of the main new features of 3.3...

Unfortunately we're stuck with the 2.x branch due to the requirement of using the .NET 4.0 version for the foreseeable future.

Or convert it to a "timestamp without time zone"

Thanks @Emill, we already did that and documented the time zones as an unsupported feature. Hope we'll get back to that one day though.