Clean up and harmonize datetime types with regards to different dialects
Description
Environment
is related to
Activity

Frédéric Delaporte October 4, 2017 at 2:22 PMEdited
Possible breaking changes:
NHibernate type DateTimeType, which is the default for a .Net DateTime, does no longer cut fractional seconds. Use DateTimeNoMsType if you wish to have fractional seconds cut. It applies to its Local/Utc counterparts too.
LocalDateTimeType and UtcDateTimeType do no more accept being set with value having a non-matching kind, they throw instead.
DbTimestamp will now round the retrieved value according to Dialect.TimestampResolutionInTicks.
When an object typed property is mapped to a NHibernate timestamp, setting an invalid object in the property will now throw at flush instead of replacing it with DateTime.Now.
SQL Server 2008+ dialects now use datetime2 instead of datetime for all date time types, including timestamp. This can be reverted with sql_types.keep_datetime setting.
SQL Server 2008+ timestamp resolution is now 100ns in accordance with datetime2 capabilities, down from 10ms previously. This can be reverted with sql_types.keep_datetime setting.
Oracle 9g+ dialects now use timestamp(7) for all date time types, instead of timestamp(4).
Oracle 9g+ timestamp resolution is now 100ns in accordance with timestamp(7) capabilities, down from 100µs previously.
String parameter length will no more be specified by the OdbcDriver.
IMapping interface has an additional Dialect member. ISessionFactoryImplementor has lost it, since it gains it back through IMapping.
IDriver.ExpandQueryParameters and DriverBase.CloneParameter take an additional argument.

Frédéric Delaporte September 27, 2017 at 11:55 PM
It looks like Oracle semantic. Its Date
type store time with seconds but without ms. For storing ms or smaller, the adequate Oracle type is timestamp
.
About Sql Server datetime
, it is a dire case, because this is not actually a 3ms resolution, but 10/3 of resolution. This causes issues, workaround by rounding to 10ms when using the timestamp
NHibernate type.

Nathan Brown September 27, 2017 at 4:12 AMEdited
Shouldn't ADO.Net worry about cutting the precision of DateTime? Any random DateTime is accurate to some 100-nanoseconds, but depending on the actual schema type, the database can store whatever part of that it wants. You are then left with the precision you get back.
In SQL Server, the old [[datetime]|https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql] is accurate to 0.003 milliseconds. It's confusing that NHibernate decides to chop it to 1 second increments when saving. What is that even buying?

Frédéric Delaporte September 23, 2017 at 10:00 PMEdited
There is two subjects: Sql Server DateTime vs DateTime2 issue, and the NHibernate DateTime which rounds to seconds, used by some where they should have use timestamp instead.
For the first, I think NHibernate DateTime2Type
(and creating derivatives for Utc, timestamp,...) is a wrong answer. We should instead default the standard portable types to use dbtype DateTime2
with SQL 2008+, and obsolete the DateTime2 NHibernate types. We may add a configuration option for reverting to DateTime
for easing migration in case DateTime2 was causing issues for those having DateTime in DB, but that would be an "obsolete" option from the start.
For the second, that is an old trap for .Net developers. I do not know how that goes on Java side, but on .Net side clearly many developers get trapped. So we may depart from the Hibernate semantic and decide that for v5.0, DateTimeType
will take the role of timestamp. What about TimestampType
then? I think it should be obsoleted. DbTimestampType
? Inherit from DateTimeType
instead. Due to minor differences I have kept it, and that will be more friendly to people used to Hibernate.
Why not having a new SmallDateTimeType
for still rounding seconds, but the name may be confusing and let believe it would map to SQL Server smalldatetime (which rounds to minutes). DateTimeNoMsType
maybe?
And those who would like to have .Net DateTime
defaulting back to cutting milliseconds could use if merged.
Then there is the Local/Utc mess. Currently, they are defined only for the millisecond cutting type. I think all types should have them.
We also need to override CurrentTimestampSQLFunctionName
for Sql 2008, because it currently uses a function which returns a datetime
, not a datetime2
. And maybe defines utc counterparts for all dialects, in order to provide a DbUtcTimestampType
.

Alex Zaytsev April 20, 2017 at 11:30 PM
I think that current TimestampType
shall be called DateTimeType
, and current DateTimeType
as SmallDateTimeType
Currently, NHibernate's handling of datetime types have some issues:
The old DateTimeType maps to SQL datetime on MS SQL server. But since 2008 MS suggests to use the datetime2 type for new development.
Support for MS SQL Server's datetime2 SQL type was added using the DateTime2Type, but this is based on using DbType.DateTime2, which is only supported by MSSQL2008Dialect and later, not by other dialects. Unlike the DateTimeType, DateTime2Type also doesn't round to whole seconds. Which is good or bad depending on what you want.
There is also LocalDateTimeType and UtcDateTimeType, but no correspondence for DateTime2.
There is also TimestampType, which cannot currently be used with SQL Server's datetime2, but since it's intended for versioning it really could benefit from the increased precision. (Actually, it may work with datetime2(3) on SQL Server 2014 and previous, but not on 2016+ because of different rounding behavior when comparing datetime and datetime2 values.
We probably want to change the sql server dialect (2008+) to use datetime2 for DbType.DateTime by default. This corresponds to how Hibernate already does it. Then DateTimeType would automatically use datetime2 where available. Probably means modifying the SqlClient2008Driver so that DbType.DateTime gets overriden with DbType.DateTime2 where needed. Then the remaining difference between DateTimeType and DateTime2Type would be that the former rounds to whole seconds, while the latter only works were DbType.DateTime2 is understood. So better to rewrite DateTime2Type to also use DbType.DateTime but without the rounding and call it DateTimeHighPrecisionType or something.