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.
