UTC all the things with NHibernate+DateTime + Postgres+timestamptz

Using Npgsql version 4.0, NHibernate can persist UTC using DateTime + timestamptz with ease. To do that, just pass the TimeZone=UTC to NHibernate's connection string.

If you are using older version of Npgsql, you cannot use TimeZone=UTC in NHibernate's connection string. Here's how to persist UTC to timestamptz with NHibernate using the older Npgsql http://www.ienablemuch.com/2018/05/persist-utc-date-correctly-with-nhibernate.html

namespace TestNhUtc
{
    using System;
    using NHibernate.Cfg;


    class Program
    {
        static async System.Threading.Tasks.Task Main(string[] args)
        {
            var cfg = new NHibernate.Cfg.Configuration();

            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.NpgsqlDriver>();
                c.Dialect<NHibernate.Dialect.PostgreSQLDialect>();

                c.ConnectionString = 
                    "Server=localhost; Port=5432; Database=test; " +
                    "User Id=postgres; Password=opensesame93; " +
                    "TimeZone=UTC";


                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;
            });


            var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
            mapper.AddMapping<TheTimeMapping>();

            var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

            cfg.AddMapping(mapping);

            var sf = cfg.BuildSessionFactory();


            /// Test code starts here            

            var utcString = "2018-05-04T17:37:00.0000000Z";

            var utc = DateTime.Parse(utcString).ToUniversalTime();
            var local = DateTime.Parse(utcString);

            var zuluFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff'Z'";
            var utcZuluFormat = utc.ToString(zuluFormat);



            int savedId;
            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var t = new Test
                {
                    UtcString = utcZuluFormat,
                    TheTimestampTz = utc,
                };

                await session.PersistAsync(t);

                await tx.CommitAsync();

                savedId = t.Id;
            }

            DateTime readDateTime;
            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var t = await session.GetAsync<Test>(savedId);

                readDateTime = t.TheTimestampTz;

                t.UtcString = utcZuluFormat;
                t.TheTimestampTz = utc;

                await tx.CommitAsync();
            }


            Console.WriteLine($@"
                {nameof(utc)}    : {utcZuluFormat} kind:{utc.Kind}
                {nameof(local)}  : {local} kind:{local.Kind}

                {nameof(readDateTime)} {readDateTime} kind:{readDateTime.Kind}

                Both resolves to same value? {readDateTime == utc} 
            ");

        }
    }

    public class Test
    {
        public virtual int Id { get; set; }

        public virtual string UtcString { get; set; }
        public virtual DateTime TheTimestampTz { get; set; }            
    }

    public class TheTimeMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
    {
        public TheTimeMapping()
        {
            Table("test");

            Id(x => x.Id, id =>
            {
                id.Generator(
                    NHibernate.Mapping.ByCode.Generators.Sequence,
                    generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq" })
                );
            });

            Property(p => p.UtcString, cm => cm.Column("utc_string"));

            Property(p => p.TheTimestampTz, cm => cm.Column("the_timestamptz"));

            DynamicUpdate(true);
            SelectBeforeUpdate(true);
        }
    }
}
/*
-- drop table test;

create table test
(
    id serial primary key,          
    utc_string text not null,
    the_timestamptz timestamptz not null
);

*/

It has a problem though, it suffers from ghost-update problem. If the consumer of your REST service sends their datetime in UTC, or a datepicker/datetimepicker sends its value in UTC e.g., 2018-05-04T17:37:00.0000000Z, the UPDATE routine would cause an update on the timestamptz column even if that column's existing value (e.g., 2018-05-05T01:37:00+08:00) resolves to "same" value as the UTC.

That is due to NHibernate's default reading of timestamptz as DateKind.Local. However, the value 2018-05-04T17:37:00.0000000Z is DateKind.Utc. NHibernate will not attempt to compare the two as the same as they are different kind of Date; consequently, NHibernate will issue an update on the timestamptz column regardless of the sameness of new value to original value.


The problem's output:

NHibernate: 
    select
        nextval ('test_id_seq')
NHibernate: 
    INSERT 
    INTO
        test
        (utc_string, the_timestamptz, Id) 
    VALUES
        (:p0, :p1, :p2);
    :p0 = '2018-05-04T17:37:00.000Z' [Type: String (0:0:0)], 
    :p1 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p2 = 10 [Type: Int32 (0:0:0)]
NHibernate: 
    SELECT
        test0_.Id as id1_0_0_,
        test0_.utc_string as utc2_0_0_,
        test0_.the_timestamptz as the3_0_0_ 
    FROM
        test test0_ 
    WHERE
        test0_.Id=:p0;
    :p0 = 10 [Type: Int32 (0:0:0)]
NHibernate: 
    UPDATE
        test 
    SET
        the_timestamptz = :p0 
    WHERE
        Id = :p1;
    :p0 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p1 = 10 [Type: Int32 (0:0:0)]

                utc    : 2018-05-04T17:37:00.000Z kind:Utc
                local  : 05/05/2018 01:37:00 kind:Local

                readDateTime 05/05/2018 01:37:00 kind:Local

                Both resolves to same value? False 

We can make NHibernate make timestamptz return its universal value, a.k.a., UTC. So if it's exactly the same as the new UTC value, NHibernate will not update the timestamptz column.

Here's how, on your table mapping, set the timestamptz field's NHibernate Type mapping to your custom type. Do note that you will still use DateTime as the type for the property, you cannot use DateTimeOffset as timestamptz (a.k.a., timestamp with time zone) does not store time zone. timestamptz's time zone is just for presentation purposes only (e.g., in psql/pgAdmin), following dates stores as the same value, the time zone offset is not stored.

test=# set timezone to 'Asia/Manila'; -- Manila is +08:00
SET
test=# 
test=# create table x (y serial primary key, z timestamptz);
CREATE TABLE
test=# 
test=# insert into x(z) values
test-# ('2018-05-04T17:37:00.000Z'),
test-# ('2018-05-05T01:37:00+08:00'),
test-# ('2018-05-05T02:37:00+09:00');
INSERT 0 3
test=# 
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-05 01:37:00+08
 2 | 2018-05-05 01:37:00+08
 3 | 2018-05-05 01:37:00+08
(3 rows)

test=# 
test=# set timezone to 'Asia/Tokyo';
SET
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-05 02:37:00+09
 2 | 2018-05-05 02:37:00+09
 3 | 2018-05-05 02:37:00+09
(3 rows)

test=# 
test=# set timezone to 'UTC';
SET
test=# select * from x;
 y |           z            
---+------------------------
 1 | 2018-05-04 17:37:00+00
 2 | 2018-05-04 17:37:00+00
 3 | 2018-05-04 17:37:00+00
(3 rows)

test=# 

I think NHibernate+timestamptz causes too much mansplaining in the Postgres community. Just like when you have to explain to someone that there is no Postgre database, it's just Postgres or PostgreSQL, as historically Postgres is a Post-Ingres database. Is there an Ingre database? Nonetheless, Postgres has only few gotchas as compared to MySQL.

Well you can use DateTimeOffset for persisting UTC to timestamptz, but NHibernate/Npgsql would throw an exception that it cannot convert DateTime to DateTimeOffset when you get database value from NHibernate/Npgsql, how would that be useful? You can write but you cannot read it back. I digress.

Here's how you can return DateTime with DateKind.Utc.

public class TheTimeMapping : NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
{
    public TheTimeMapping()
    {
        Table("test");

        Id(x => x.Id, id =>
        {
            id.Generator(
                NHibernate.Mapping.ByCode.Generators.Sequence,
                generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq" })
            );
        });

        Property(p => p.UtcString, cm => cm.Column("utc_string"));

        Property(p => p.TheTimestampTz, cm =>
        {
            cm.Column("the_timestamptz");

            // Here's how
            cm.Type<Infra.CustomUtcType>();
        });

        DynamicUpdate(true);
        SelectBeforeUpdate(true);
    }
}

Here is the Infra.CustomUtcType definition:

namespace Infra
{
    [System.Serializable]
    public class CustomUtcType : NHibernate.UserTypes.IUserType
    {
        bool NHibernate.UserTypes.IUserType.IsMutable => false;

        System.Type NHibernate.UserTypes.IUserType.ReturnedType => typeof(System.DateTime);

        NHibernate.SqlTypes.SqlType[] NHibernate.UserTypes.IUserType.SqlTypes => 
            new [] { new NHibernate.SqlTypes.SqlType(System.Data.DbType.DateTime) };

        object NHibernate.UserTypes.IUserType.Assemble(object cached, object owner) => cached;

        object NHibernate.UserTypes.IUserType.DeepCopy(object value) => value;

        object NHibernate.UserTypes.IUserType.Disassemble(object value) => value;

        object NHibernate.UserTypes.IUserType.Replace(object original, object target, object owner) => original;

        int NHibernate.UserTypes.IUserType.GetHashCode(object x) => x == null ? 0 : x.GetHashCode();

        void NHibernate.UserTypes.IUserType.NullSafeSet(
            System.Data.Common.DbCommand cmd, 
            object value, 
            int index,
            NHibernate.Engine.ISessionImplementor session
        ) 
        => cmd.Parameters[index].Value = value != null ? value: System.DBNull.Value;

        bool NHibernate.UserTypes.IUserType.Equals(object x, object y)
        =>
            x == null && y == null ?
                true
            : x == null || y == null ?
                false
            :
                ((System.DateTime)y).Equals(x);
        

        object NHibernate.UserTypes.IUserType.NullSafeGet(
            System.Data.Common.DbDataReader rs, string[] names,
            NHibernate.Engine.ISessionImplementor session, 
            object owner
        )
        =>
            names.Length != 1 ? 
                throw new System.InvalidOperationException("Only expecting one column...")
            : rs[names[0]] is System.DateTime value ? 
                value.ToUniversalTime() 
            : 
                (System.DateTime?) null;        
    }
}

No more ghost-update problem, yay!

NHibernate: 
    select
        nextval ('test_id_seq')
NHibernate: 
    INSERT 
    INTO
        test
        (utc_string, the_timestamptz, Id) 
    VALUES
        (:p0, :p1, :p2);
    :p0 = '2018-05-04T17:37:00.000Z' [Type: String (0:0:0)], 
    :p1 = 2018-05-04T17:37:00.0000000Z [Type: DateTime (0:0:0)], 
    :p2 = 11 [Type: Int32 (0:0:0)]
NHibernate: 
    SELECT
        test0_.Id as id1_0_0_,
        test0_.utc_string as utc2_0_0_,
        test0_.the_timestamptz as the3_0_0_ 
    FROM
        test test0_ 
    WHERE
        test0_.Id=:p0;
    :p0 = 11 [Type: Int32 (0:0:0)]

                utc    : 2018-05-04T17:37:00.000Z kind:Utc
                local  : 05/05/2018 01:37:00 kind:Local

                readDateTime 05/04/2018 17:37:00 kind:Utc

                Both resolves to same value? True