[#NH-57] Type.TimeType & DBType.Time is diff depending on Data Provider

NHibernate

Type.TimeType & DBType.Time is diff depending on Data Provider

Created: 04/May/04 01:31 PM Updated: 03/Nov/04 09:44 PM

Component/s:

Core

Affects Version/s:

prealpha, alpha

Fix Version/s:

alpha

Description

« Hide

The OleDbClient throws exceptions when you attempt to put a DateTime into a parameter that expects a DbType.Time.

If I change Type.TimeType to hold a TimeSpan then:
=====================================================
The SqlClient will throw an exception when putting a TimeSpan value into a parameter that holds a DbType.Time -> it is trying to put a TimeSpan into a DateTime value.

The OleDbClient adds the TimeSpan to the current day. If you set a DateTime to the value of a parameter that has a type of DbType.Time it will throw an exception.

No idea what other drivers do...

I'm inclined to leave it as it is and just say there is limited Driver support for it. Don't use it unless you know that your db can handle a DbType.Time for storing a DateTime.

If you need to store a Time in the format of "hh:mm:ss" then I suggest writing a User Type. The best way to store time (meaning the difference between a start point and end point) is to use the TimeSpan type because it stores it in Ticks (int64)

All

Comments

Change History

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Mike Doerfler - 04/May/04 01:32 PM

Peter, it looks like some of the mappings have a type="Time" type. Would it bother you if I changed them to a type="TimeSpan"?

[ Permlink | « Hide ]

Peter Smulovics - 05/May/04 05:32 AM

I don't understand the problem, could you clear it a bit?

[ Permlink | « Hide ]

Mike Doerfler - 05/May/04 06:54 AM

The problem is with the OleDb driver and the tests where a TimeType is involved.

I am not sure how other db's like Oracle will handle a parameter of DbType.Time. The SqlClient Data Provider converts that parameter type to a DateTime, the OleDb Data Provider treats it differently.

[ Permlink | « Hide ]

Mike Doerfler - 25/Oct/04 12:20 AM

Duplicate of ~~NH-89~~

FooBarTest.PersistCollections

there is code commented out dealing with a TimeArray. Need to figure out what the TimeType in nh should map to in .net. Right now it is mapping to a DateTime and only storing the Time as significant.

I think this implementation maps to the java spec of java.sql.Time but am not sure about it.

[ Permlink | « Hide ]

Mike Doerfler - 28/Oct/04 11:49 AM

MySql has its own implementation of DbType.Time. MSDN docos say:
Time
-----------------------
Date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds.

MySql converts DbType.Time to MySqlDbType.Time. The MySql docs say:
Time
-----------------------
Time
The range is '-838:59:59' to '838:59:59'.

[ Permlink | « Hide ]

Mike Doerfler - 28/Oct/04 12:29 PM

To make this more consistent with the msdn docs I think this is how I'll implement it.

The property will be a TimeSpan.
The database column will be a DbType.Time (according to msdn docs this should be a datetime column)
The zero date is "1753-01-01".

The value stored in the db will be "1753-01-01" + value of TimeSpan property.

Isolate any Type="Time" mappings to NHSpecific namespace so FooBar tests stop failing for drivers that don't implement DbType.Time consistenty with msdn docs.

[ Permlink | « Hide ]

Mike Doerfler - 02/Nov/04 08:36 PM

I ended up going with the MSDN docs kind of. TimeType returns the hour, minute, second portion into a DateTime on Jan 1, 1753. It only compares hour, minute, second for dirty.

I'm still not that happy with implementation because the msdn spec and how dbs are implementing it are way off. But I don't see it being used very often. Same comments can apply to DateType.

[ Permlink | « Hide ]

Mike Doerfler - 03/Nov/04 09:44 PM

deployed with build 0.4