datetime, datetime2 and timestamp over various dialects

[Oskar Berggren]

Hi,

I'm trying to understand the best way (and any room for improvement) to handle timestamps with high resolution running over different database dialects.

With todays NHibernate, what is the cleanest way to store timestamps with resolution at least 1ms that will work for both MSSQL and SQLite?

This is the default for a .Net DateTime property:
DateTimeType
/// This only stores down to a second, so if you are looking for the most accurate
/// date and time storage your provider can give you use the <see cref="TimestampType" />.


DateTime2Type

Uses DbType.DateTime2 and datetime2 on MS SQL Server. Fails for everything else it seems, so I cannot cleanly use it in mappings and have it run on MSSQL in production and SQLite in tests.

TimestampType
Is recommended by DateTimeType if an accuracy greater than 1 second is required and has the following documentation "stores it to the accuracy the database supports, and will default to the value of DateTime.Now if the value is null."

The problem is that I'm not talking about a versioning property here - I _want_ null values to be stored as null so TimestampType doesn't fit. Because of this null-handling I think it's a bit strange that this type is recommended by DateTimeType for high resolution.

Nowadays, the "stores it to the accuracy the database supports" is actually a lie, because it will round to 10ms on MS SQL Server instead of using the datetime2 type.

/Oskar

[Richard Birkby]

On a system that ran on SQLCE, MSSQL, Oracle I used to just remap the datatype in the MSSQL driver to allow storage of dates prior to 1753. So something like:

public override void AdjustCommand(IDbCommand command)

{

       foreach (IDbDataParameter parameter in command.Parameters)

        {

            if (parameter.DbType == DbType.DateTime)

            {

                parameter.DbType = DbType.DateTime2;

                parameter.Size = 8;

            }

        }

}

NHibernate always felt it was almost, but not quite there with dates.

Richard

--

---
You received this message because you are subscribed to the Google Groups "nhibernate-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhibernate-develo...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jeffrey Becker]

The System.Data.SQLite driver pre-dates date-time support in SQLite and implements a bunch of different date/time storage formats controlled by the DateTimeFormat parameter to the connection string valid values are:

Ticks - Use the value of DateTime.Ticks.

ISO8601 (Default) - Use the ISO-8601 format.  Uses the "yyyy-MM-dd HH:mm:ss.FFFFFFFK" format for UTC DateTime values and "yyyy-MM-dd HH:mm:ss.FFFFFFF" format for local DateTime values.

JulianDay - The interval of time in days and fractions of a day since January 1, 4713 BC.

UnixEpoch - The whole number of seconds since the Unix epoch (January 1, 1970).

InvariantCulture - Any culture-independent string value that the .NET Framework can interpret as a valid DateTime.

CurrentCulture - Any string value that the .NET Framework can interpret as a valid DateTime using the current culture.

Given that the default format uses 7 decimal places for fractions of a second I dont see why SQLite doesnt support that data format.

[Oskar Berggren]

Using DateTime2Type in the mappings it works for MSSQL. I was able to run the tests on SQLite by adding this line to the dialect:
    RegisterColumnType(DbType.DateTime2, "DATETIME");

I expected that I would also need to have the SQLite driver map DbType.DateTime2 back to DbType.DateTime but that wasn''t required. It seems at least System.Data.SQLite 1.0.74 will understand DbType.DateTime2 as the parameter type.

Probably we should make all dialects understand DbType.DateTime2 and map it to a suitable column type. Then there is also the fact that since MSSQL 2008, MS recommends using datetime2 for new applications instead of datetime. We should explore changing the NHibernate default for NH5.0.

With the above two changes we should eventually end up with this:

DateTimeType uses the best possible column type but truncates values to second precision.

DateTime2Type uses the best possible column type and stores the value with the highest precision available. Unlike now, it should work on all dialects. We might also want to allow "datetime2(precision)" syntax.

I don't really like the "2" however - it's an MS-ism leaking through the layers. Perhaps we can just call it DateTimeHiResType or something.

/Oskar

[CSharper]

As SQL Server 2005 and before are getting really old now and breaking changes might be risked for NHib 5.0 I would even like a solution where DateTimeType means the "best" date and time data type of a DBMS.

Things are currently all that strange because ADO.NET DbType enum is quite streamlined for MS SQL Server and makes things complicated when working with different databases or current SQL Server versions.

If...

- DateTimeType meant DbType.DateTime on most databases but DbType.DateTime2 on SQL Server 2008 and after

- We had precision for those DBMS that support it

- We had a separate LegacyDateTime data type that is exactly the old one

... those users that do not want to migrate their systems just would need to adjust the mappings to use the legacy type...

... and the others could use all the benefits of the vendors' preferred data types including defined precsions.

[Oskar Berggren]

2014-09-17 9:06 GMT+02:00 CSharper <csharp...@googlemail.com>:

As SQL Server 2005 and before are getting really old now and breaking changes might be risked for NHib 5.0 I would even like a solution where DateTimeType means the "best" date and time data type of a DBMS.

Things are currently all that strange because ADO.NET DbType enum is quite streamlined for MS SQL Server and makes things complicated when working with different databases or current SQL Server versions.

If...

- DateTimeType meant DbType.DateTime on most databases but DbType.DateTime2 on SQL Server 2008 and after

- We had precision for those DBMS that support it

- We had a separate LegacyDateTime data type that is exactly the old one

... those users that do not want to migrate their systems just would need to adjust the mappings to use the legacy type...

... and the others could use all the benefits of the vendors' preferred data types including defined precsions.

This is almost exactly what I said. :)  I.e. DateTimeType would use datetime2 on MSSQL2008+. However, the DateTimeType itself also rounds to whole seconds, regardless of database system. This behaviour should be preserved.

Regarding legacy behaviour I would prefer if this could be activated by a dialect configuration, instead of changing the mappings.

/Oskar