Map DbType.DateTime to timestamptz

[roji]

There are 3 date/time-related values in the System.Data DbType enum:

• DbType.DateTime. Currently mapped to timestamp without time zone. Discouraged for use for columns on SQL Server.
• DbType.DateTime2. Currently mapped to timestamp without time zone as well. Encouraged for use for columns on SQL Server.
• DbType.DateTimeOffset. Currently mapped to timestamp with time zone. There is no actual type that corresponds to DateTimeOffset in PostgreSQL.

As part of our timestamp redo for 6.0, we'll change DbType.DateTime to map to timestamp with time zone. Reasoning:

• We believe the default PG type wanted in most scenarios is timestamptz (UTC timestamp), DbType.DateTime expresses that.
• Both linq2db and Dapper set DbType.DateTime by default when they send a DateTime, this enforces DbType.DateTime as the "default" option (but see this issue where Dapper stopped setting DbType altogether for DateTime, to allow Npgsql to infer from the Kind).
• Users are likely to choose either DbType.DateTime or DateTime2 for sending a timestamp. Having both map to timestamp without time zone seems like a pit of failure; this would throw if a UTC DateTime is provided, but if a DbType.DateTime parameter is inserted into a timestamptz column, a silent time zone conversion would be done on the PG side instead.
• We considered mapping the other way around (DateTime2 -> timestamptz, DateTime -> timestamp), since in SQL Server DateTime2 is the default. However, it seems that while DateTime2 is the default column type, DateTime is routinely used when sending parameters (see especially linq2db and Dapper).