2014-09-17 9:06 GMT+02:00 CSharper <[email protected]>: > 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 > > 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]>: >> >> 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. > -- --- 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.
