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?
DateTime/Offset treats UTC as Local
Re: DateTime/Offset treats UTC as Local
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";