Reason why NHibernate persist "wrong" data from DateTime property to timestamptz field

namespace TestNpgsql
{
    using System;
    using Npgsql;
    using NpgsqlTypes;
    using System.Data;

    class Program
    {
        static readonly string utcString = "2018-05-04T17:37:00.0000000Z";

        static readonly DateTime local = DateTime.Parse(utcString);
        static readonly DateTime utc = DateTime.Parse(utcString).ToUniversalTime();

        static string insertTest = "insert into test(a, b, c, d) values(:a, :b, :c, :d)";
        
        static void Main(string[] args)
        {                    
            using (var conn = 
                   new NpgsqlConnection("Server=127.0.0.1;Database=test;User Id=postgres;Password=opensesame93;")
                  )

            {
                conn.Open();
                
                Console.WriteLine($"utc string {utcString}");
                Console.WriteLine($"local value is {local} kind is {local.Kind}");
                Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");
                
                var cmd = new NpgsqlCommand(@"insert into test(a, b, c, d) values(:a, :b, :c, :d)", conn);
                
                
                Insert(conn);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"                
                                                                
                
                Insert(conn, DbType.DateTimeOffset); 
                //     "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, DbType.DateTime); // now I see why NHibernate is "wrong"
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
                
                
                Insert(conn, NpgsqlDbType.TimestampTZ);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, NpgsqlDbType.Timestamp);
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
            }                                         
        }
        
         
        static void Insert(NpgsqlConnection conn)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);

            cmd.Parameters.AddWithValue("a", local);
            cmd.Parameters.AddWithValue("b", utc);
            cmd.Parameters.AddWithValue("c", local);
            cmd.Parameters.AddWithValue("d", utc);

            cmd.ExecuteNonQuery();
        }
        
        static void Insert(NpgsqlConnection conn, DbType dbType)
        {            
            var cmd = new NpgsqlCommand(insertTest, conn);
            
            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", DbType = dbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", DbType = dbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }
        

        static void Insert(NpgsqlConnection conn, NpgsqlDbType npgsqlDbType)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);     
            
            Console.WriteLine($"local value is {local} kind is {local.Kind}");
            Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");

            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", NpgsqlDbType = npgsqlDbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", NpgsqlDbType = npgsqlDbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }

              
    }
}

/*
 
-- drop table test;
create table test
(
    id int generated by default as identity primary key,
    a timestamp not null,
    b timestamp not null,
    c timestamptz not null,
    d timestamptz not null
);
 
 */

UPDATE June 1

The latest version of Npgsql, version 4.0, has TimeZone=UTC parameter to connection string.

Without UTC parameter.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  2 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  4 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
(5 rows)

The TimeZone=UTC connection string parameter fixes the DateTime + timestamptz problem in NHibernate. The InitializeParameter fix won't be needed anymore.

AS of the time of this writing, NHibernate has an embedded Npgsql 3.2.7. Just add Npgsql 4.0 to an NHibernate project so you'll be able to use TimeZone=UTC in the connection string.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  2 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  4 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08