I am using the "timestamp with time zone" column type and setting my datetimes like:
INSERT INTO mytable(col1)
VALUES(timestamp with time zone '2020-07-16 17:45:00.000000+00');
Whenever I try and retrieve my DateTimes they are being converted to the 'local' kind.
This is not what I want, its set as a UTC datetime and I want it back as UTC, I do not want it converting to local going into the database, nor coming back out ... doing so makes it impossible to get to UTC without causing ambiguity.
I have tried setting the "PGTZ" environment variable; I have also executed the following sql when the connection opens:
Connection.ExecuteAsync( "SET TIMEZONE TO 'UTC'" );
Nothing I have tried seems to work and it seems to be by-design according to various posts like: https://github.com/npgsql/npgsql/issues/347
This seems utterly wrong to me, but I am hoping someone can point me to a workaround for this.
I am using .net5/asp.net and Dapper (not EFCore) and Npgsql version 4.1.5.
This is indeed by design, and matches the PostgreSQL when querying timestamptz values in regular text queries. If you use fire up psql or pgadmin and select a timestamptz column, you'll see a local timestamp based on on your TimeZone session parameter.
As mentioned by @adrian-klaver, timestamptz isn't about storing the timezone in the database - it's about applying timezone conversions when reading and writing timestamp values to the database, based on the TimeZone parameter. If you don't want any such conversions, consider switching to timestamp (without time zone), and having all timestamps as UTC by convention.