title: NHibernate keeps losing the timezone of my DateTime in PostgreSQL 9.0
body: <p>I need to convince NHibernate to store the timezone of the <code>DateTime</code> values I am persisting. The columns are <code>timestamp with time zone</code>. </p>
  
  <p>We have a convention that we only store UTC DateTimes in the database, so we were using <code>timestamp without time zone</code> but I needed the database to represent know the timezone so a report that access the database directly would be able to perform correctly.</p>
  
  <p>I've tried telling NHibernate I have a custom type for <code>DateTime</code> as follows:</p>
  
  <pre><code>    [Serializable]
      public class UtcDateTimeUserType&lt;T&gt; : IUserType
      {
          private static readonly SqlType[] MySqlTypes = {SqlTypeFactory.DateTime };
  
          public SqlType[] SqlTypes
          {
              get { return MySqlTypes; }
          }
  
          public System.Type ReturnedType
          {
              get { return typeof(T); }
          }
  
          public bool IsMutable
          {
              get { return false; }
          }
  
          public object DeepCopy(object value)
          {
              return value; // DateTime is immutable
          }
  
          public new bool Equals(object x, object y)
          {
              return object.Equals(x, y);
          }
  
          public object NullSafeGet(IDataReader resultSet,
                                    string[] names,
                                    object owner)
          {
              int index0 = resultSet.GetOrdinal(names[0]);
              if (resultSet.IsDBNull(index0))
              {
                  return null;
              }
              var value = resultSet.GetDateTime(index0);
              return DateTime.SpecifyKind(value, DateTimeKind.Utc);
          }
  
          public void NullSafeSet(IDbCommand statement,
                                  object value,
                                  int index)
          {
              if (value == null)
              {
                  ((IDbDataParameter)statement.Parameters[index]).Value = DBNull.Value;
              }
              else
              {
                  var dateTime = (DateTime)value;
                  if (dateTime == DateTime.MinValue) dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
                  if (dateTime.Kind == DateTimeKind.Unspecified)
                      throw new InvalidOperationException(
                          "Attempting to save a datetime without a Kind set.. what time zone are you in?");
                  ((IDbDataParameter) statement.Parameters[index]).Value = dateTime.ToUniversalTime();
  
              }
          }
  
          public object Disassemble(object value)
          {
              return value;
          }
  
          public object Assemble(object cached, Object owner)
          {
              return cached;
          }
  
          public object Replace(object original, object target, object owner)
          {
              return original;
          }
  
          public int GetHashCode(Object x)
          {
              return x.GetHashCode();
          }
      }
  
      public class NullableUtcDateTimeUserTypeConvention
    : UserTypeConvention&lt;UtcDateTimeUserType&lt;DateTime?&gt;&gt;
      {
      }
  
      public class UtcDateTimeUserTypeConvention
    : UserTypeConvention&lt;UtcDateTimeUserType&lt;DateTime&gt;&gt;
      {
      }
  </code></pre>
  
  <h3>Fluent NHibernateConfiguration</h3>
  
  <pre><code>    autoPersistenceModel = autoPersistenceModel.Conventions.Setup(s =&gt;
                                 {
                                     s.Add&lt;TableNameConvention&gt;();
                                     s.Add&lt;PrimaryKeyConvention&gt;();
                                     s.Add&lt;CustomForeignKeyConvention&gt;();
                                     s.Add&lt;CascadeConvention&gt;();
                                     s.Add&lt;CalculatedFieldPropertyConvention&gt;();
                                     s.Add&lt;UtcDateTimeUserTypeConvention&gt;();
                                     s.Add&lt;NullableUtcDateTimeUserTypeConvention&gt;();
                                     s.AddAssembly(assembly);
                                 });
  </code></pre>
  
  <p>This catches code trying to persist datetimes without The Timezone set. It also ensures all <code>DateTime</code>s are UTC before going into the database, and correctly sets them to UTC on the way back out. </p>
  
  <p>But the following statement does not work:</p>
  
  <pre><code>((IDbDataParameter) statement.Parameters[index]).Value = dateTime.ToUniversalTime();
  </code></pre>
  
  <p>This is still <code>Unspecified</code> after assignment:</p>
  
  <pre><code>((DateTime)((IDbDataParameter) statement.Parameters[index]).Value).Kind
  </code></pre>
  
  <p>I notice from NHibernate's code that the PostgresDialect has the following:</p>
  
  <pre><code> RegisterColumnType(DbType.DateTime, "timestamp");
  </code></pre>
  
  <p>but nothing mentions <code>timestamp with time zone</code> or <code>timestamptz</code>.</p>
  
  <p>How to I make NHibernate persist DateTimes with their time zone into Postgres9?</p>