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. Am Samstag, 13. September 2014 12:54:45 UTC+2 schrieb 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 > > > 2014-09-12 21:46 GMT+02:00 Jeffrey Becker <[email protected] > <javascript:>>: > >> 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. >> >> On Wednesday, September 10, 2014 11:54:29 AM UTC-4, Oskar Berggren wrote: >>> >>> 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 >>> >> >> > -- --- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
