DateTime/Offset treats UTC as Local

[shelakel]

I'm busy evaluating dotConnect - Postgres Professional for potential use at my company and I've come upon an issue regarding the treatment of DateTime/DateTimeOffset at Insert/Update.

Some context:
I have a database with its time zone set to UTC: ALTER DATABASE test SET timezone TO 'UTC';
I have created a user and altered it to also be at UTC: ALTER ROLE test_user SET timezone = 'UTC';

The user time zone is used for the connection, so it's not necessary to execute "SET TIME ZONE 'UTC';" when opening a DbContext (although I've tried this also).

I've got a simple table on the test database:
CREATE TABLE test (
id serial PRIMARY KEY NOT NULL
, created_at timestamptz NOT NULL
, CHECK (EXTRACT(TIMEZONE FROM created_at) = '0')
);

The check will fail if a time is supplied that's not at UTC. This check doesn't fail, so a proper time value at UTC is supplied.

When I do an insert using Entity Framework (CreatedAt = DateTimeOffset.UtcNow or DateTime.UtcNow), CreatedAt is treated as if it's local time.

My time zone is UTC +02:00, so if it's 10:00 AM (UTC+2) here, the UTC value would be 08:00 AM (UTC). supply the UTC DateTime/DateTimeOffset at 08:00AM (UTC), it then inserts it into the database as 06:00 AM (UTC-2) instead of 08:00 AM (UTC).

I'm only referring to the insert/update and viewing of the time values here. I'm using ObjectContext.ObjectMaterialized to change the time values to UTC (ex. EF would return 08:00 AM (UTC+2), even though the value is actually 08:00 AM (UTC)).

Is there something I am missing or is it a bug?

[Shalex]

Please use the TimeZone property of a static PgSqlLocalization class which is implemented to adjust timezone of the corresponding .NET data types when converting them to/from the server data types:

Code: Select all

PgSqlLocalization.TimeZone = "+0";