Fluent Nhiberhate And Missing Milliseconds

Question

I am using Fluent Nhibernate and Nhibernate for my current project. I need to record the time to the millisecond. I have this for my mapping

            Map(x => x.SystemDateTime)
            .CustomType("Timestamp")
            .Not.Nullable();

I genertaed the hbm.xml files and the line is the following:

<property name="SystemDateTime" type="Timestamp">
  <column name="SystemDateTime" not-null="true" />
</property>

I have read this is the fix, but the records in the database do not have the milliseconds. Has anyone solved this issue. And I have tried CustomSqlType also.

Thanks

Answer 1

This did not work for me:

Map(x => x.DateCreated).Column("DATE_CREATED").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Not.Nullable();

while this did:

Map(x => x.DateCreated).Column("DATE_CREATED").CustomType("timestamp").CustomSqlType("TIMESTAMP(6) WITH TIME ZONE").Not.Nullable();

I have no idea why, though :/

Answer 2

We use the same approach as you and it does correctly store the milliseconds. If you weren't always doing it that way though your old records will have lost their milliseconds.

Assuming you want to store milliseconds for all of your DateTime fields, you could use a convention:

public class OurPropertyConventions : IPropertyConvention
{
    public void Apply(IPropertyInstance instance)
    {
        Type type = instance.Property.PropertyType;
        if (type == typeof(DateTime) || type == typeof(DateTime?))
            instance.CustomType("Timestamp");
    }
}

Your mappings can now just be:

Map(x => x.SystemDateTime).Not.Nullable();

Answer 3

Revisiting this, as previous answers were slightly incomplete.

Assuming you want all your DateTime fields stored with full details and millisecond precision, use TIMESTAMP(6) WITH TIME ZONE as the SQL column type. You'll need a property convention for FluentNHibernate:

using System;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.AcceptanceCriteria;
using FluentNHibernate.Conventions.Inspections;
using FluentNHibernate.Conventions.Instances;

internal class DateTimeMapsToTimestampConvention
    : IPropertyConvention, IPropertyConventionAcceptance
{
    public void Apply(IPropertyInstance instance)
    {
        instance.CustomType<NHibernate.Type.TimestampType>();
    }

    public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
    {
        criteria.Expect(x => x.Type == typeof(DateTime)
            || x.Type == typeof(DateTime?));
    }
}

You then add this convention to your fluent configuration session factory building code:

var factory =  Fluently.Configure().Mappings(
  m => assemblies.ForEach(
    assembly => m.FluentMappings.AddFromAssembly(assembly)
                .Conventions.Add(new DateTimeMapsToTimestampConvention())))
            .BuildSessionFactory();