title: What is best practice for representing time intervals in a data warehouse?
body: <p>In particular I am dealing with a Type 2 <a href="http://en.wikipedia.org/wiki/Slowly_changing_dimension" rel="nofollow noreferrer">Slowly Changing Dimension</a> and need to represent the time interval a particular record was active for, i.e. for each record I have a <em>StartDate</em> and an <em>EndDate</em>. My question is around whether to use a <em>closed</em> (<em>[StartDate,EndDate]</em>) or <em>half open</em> (<em>[StartDate,EndDate)</em>) interval to represent this, i.e. whether to include the last date in the interval or not. To take a concrete example, say record 1 was active from day 1 to day 5 and from day 6 onwards record 2 became active. Do I make the EndDate for record 1 equal to 5 or 6?</p>
  <p>Recently I have come around to the way of thinking that says half open intervals are best based on, inter alia, <a href="http://www.cs.utexas.edu/users/EWD/transcriptions/EWD08xx/EWD831.html" rel="nofollow noreferrer">Dijkstra:Why numbering should start at zero</a> as well as the conventions for array slicing and the <em>range()</em> function in Python. Applying this in the data warehousing context I would see the advantages of a half open interval convention as the following:</p>
  <ul>
  <li><em>EndDate-StartDate</em> gives the time the record was active</li>
  <li>Validation: The <em>StartDate</em> of the next record will equal the <em>EndDate</em> of the previous record which is easy to validate.</li>
  <li>Future Proofing: if I later decide to change my granularity from daily to something shorter then the switchover date still stays precise. If I use a closed interval and store the EndDate with a timestamp of midnight then I would have to adjust these records to accommodate this.</li>
  </ul>
  <p>Therefore my preference would be to use a half open interval methodology. However if there was some widely adopted industry convention of using the closed interval method then I might be swayed to rather go with that, particularly if it is based on practical experience of implementing such systems rather than my abstract theorising.</p>