Persist UTC date correctly with NHibernate

I encountered a problem with NHibernate where the UTC date from a DateTime property is not correctly persisting to timestamptz. The utc 2018-05-04T17:37:00.000Z was persisted as 2018-05-04 17:37:00+08 to timestamptz field, which is incorrect.

The solution is to override the DateTime type and make NHibernate treat it as DateTimeOffset.

namespace AspNetCoreExample.Infrastructure.NHibernateNpgsqlInfra
{
    using System.Data.Common;

    using NHibernate.SqlTypes;
    using Npgsql;

    public class NpgsqlDriverExtended : NHibernate.Driver.NpgsqlDriver
    {        
        // this gets called when an SQL is executed
        protected override void InitializeParameter(DbParameter dbParam, string name, SqlType sqlType)
        {            
            if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter)
            {
                this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
            }
            else
            {
                base.InitializeParameter(dbParam, name, sqlType);
                
                if (sqlType.DbType == System.Data.DbType.DateTime)
                {
                    dbParam.DbType = System.Data.DbType.DateTimeOffset;
                }
            }
        }

        // NpgsqlExtendedSqlType is used for Jsonb
        protected virtual void InitializeParameter(NpgsqlParameter dbParam, string name, NpgsqlExtendedSqlType sqlType)
        {
            if (sqlType == null)
            {
                throw new NHibernate.QueryException(string.Format("No type assigned to parameter '{0}'", name));
            }

            dbParam.ParameterName = FormatNameForParameter(name);
            dbParam.DbType        = sqlType.DbType;
            dbParam.NpgsqlDbType  = sqlType.NpgDbType;
        }               
    }
}

With the code above, the utc 2018-05-04T17:37:00.000Z is now correctly persisting as 2018-05-05 01:37:00+08.

Sample wiring of NpgsqlDriver: https://github.com/MichaelBuen/AspNetCoreExample/blob/b78b97f085730cfb9494c3ec15085cccf7f761be/AspNetCoreExample.Ddd.Mapper/_TheMapper.cs#L32


If you are using Npgsql version 4.0 with NHibernate, you don't need this fix anymore. With Npgsql version 4.0, you can just add TimeZone=UTC to NHibernate's connection string. An example: http://www.ienablemuch.com/2018/06/utc-all-things-with-nhibernate-datetime-postgres-timestamptz.html