Not so much a question as I challenge a colleague of mine was presented yesterday by a DBA. We have a TimeSpan property on one of our objects which HAS to be persisted. Yes, you could just infer the value from the Start and End DateTime properties on the object but the DBA is adamant that this value is saved on the database table.
So the Oracle data type chosen by the DBA to hold the value is INTERVAL DAY(2) TO SECOND(6).
The corresponding type in Oracle.DataAccess is OracleDbType.InvervalDS but I've not been able to find anything relating to how to map that with NHibernate.
We ended up with this solution
public class SomeTimeSpanTestClass
{
public virtual string TimeSpanTest { get; protected set; }
public virtual TimeSpan ActualTimeSpan
{
get
{
// Need to do some formatting of TimeSpanTest before it can be parsed
return TimeSpan.Parse(TimeSpanTest);
}
set
{
TimeSpanTest = string.Format("{0}{1} {2}:{3}:{4}.{5}",
value.ToString().Contains('-') ? "-" : "+",
value.Days.ToString().Contains('-') ? value.Days.ToString().Substring(1).PadLeft(2, '0') : value.Days.ToString().PadLeft(2, '0'),
value.Hours.ToString().Contains('-') ? value.Hours.ToString().Substring(1).PadLeft(2, '0') : value.Hours.ToString().PadLeft(2, '0'),
value.Minutes.ToString().Contains('-') ? value.Minutes.ToString().Substring(1).PadLeft(2, '0') : value.Minutes.ToString().PadLeft(2, '0'),
value.Seconds.ToString().Contains('-') ? value.Seconds.ToString().Substring(1).PadLeft(2, '0') : value.Seconds.ToString().PadLeft(2, '0'),
value.Milliseconds.ToString().Contains('-') ? value.Milliseconds.ToString().Substring(1).PadLeft(6, '0') : value.Milliseconds.ToString().PadLeft(6, '0')
);
}
}
}
With the mapping as
<property name="TimeSpanTest" column="TIMESPAN_TEST"/>
A very noddy test as
class Program
{
static void Main(string[] args)
{
SomeTimeSpanTestClass spanClass = new SomeTimeSpanTestClass();
DateTime start = DateTime.Now;
DateTime end = DateTime.Now.AddMinutes(75);
spanClass.ActualTimeSpan = end.Subtract(start);
Console.WriteLine(spanClass.TimeSpanTest);
}
}
Obviously this code isn't refactored in any way, but for the purpose of this test it's trivial anyway.
The value in the database basically has to look like this "+00 01:15:03.000874". A - sign is also valid at the start of the string if the value is negative. An important point to note here is: when the value is negative, each part of the TimeSpan object is negative when looked at in isolation, hence the not so pretty "value.Days.ToString().Contains('-')" in each section of the Format() method.
Ours tests pass, we are able to save and retrieve a TimeSpan value into a database column defined as INTERVAL DAY(2) TO SECOND(6) via NHibernate.
If anyone has done this before a better way I'd be very interested to know how.
Apologies for not linking the Oracle types, it's my first post so I'm not allowed...
See Question&Answers more detail:
os