title: NHibernate mapping for Oracle INTERVAL DAY TO SECOND datatype
body: <p>Not so much a question as I challenge a colleague of mine was presented yesterday by a DBA. We have a <a href="http://msdn.microsoft.com/en-us/library/system.timespan.aspx" rel="nofollow">TimeSpan</a> property on one of our objects which HAS to be persisted. Yes, you could just infer the value from the Start and End <a href="http://msdn.microsoft.com/en-us/library/aa326680%28v=vs.71%29.aspx" rel="nofollow">DateTime</a> properties on the object but the DBA is adamant that this value is saved on the database table.</p>
  
  <p>So the Oracle data type chosen by the DBA to hold the value is INTERVAL DAY(2) TO SECOND(6).</p>
  
  <p>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.</p>
  
  <p>We ended up with this solution</p>
  
  <pre><code>    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')
                  );
              }
          }
      }
  </code></pre>
  
  <p>With the mapping as</p>
  
  <pre><code>    &lt;property name="TimeSpanTest" column="TIMESPAN_TEST"/&gt;
  </code></pre>
  
  <p>A very noddy test as</p>
  
  <pre><code>    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);        
  
          }
      }
  </code></pre>
  
  <p>Obviously this code isn't refactored in any way, but for the purpose of this test it's trivial anyway.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>If anyone has done this before a better way I'd be very interested to know how.</p>
  
  <p>Apologies for not linking the Oracle types, it's my first post so I'm not allowed...</p>