4

When implementing temporal datetime pairs, which is the better approach?

Approach 1

_RK   FROM_DTTM              TO_DTTM 
1     01-JAN-2012 00:00:00   31-DEC-2012 23:59:59 ------------> Old record closed out 
1     01-JAN-2013 00:00:00   31-DEC-4949 23:59:59 ------------> New active record

Used with a filter clause as follows:

 where FROM_DTTM <= &FILTER_DATE <= TO_DTTM

Approach 2

_RK   FROM_DTTM              TO_DTTM 
1     01-JAN-2012 00:00:00   01-JAN-2013 00:00:00 ------------> Old record closed out 
1     01-JAN-2013 00:00:00   31-DEC-4949 23:59:59 ------------> New active record

Used with a filter clause as follows:

 where FROM_DTTM <= &FILTER_DATE < TO_DTTM

Considerations:

Approach 1 is compatible with BETWEEN clause syntax (inclusive range). For this reason I think that it is the best approach, however - and this is my concern - there will always exist a gap between the closing and opening timestamps! Eg the following will (incorrectly from a business perspective) return nothing:

 where FROM_DTTM <= '31-DEC-2012 23:59:59.1'dt <= TO_DTTM

Different databases will have differing levels of precision here. I guess there would have to be standard (always round to the nearest second when filtering) but just wondered if there were any other reasons to support Approach 1 (such as proposed bi-temporal 'AS OF' syntax compatibility)? Or indeed, whether (and why) Approach 2 would be preferable..

Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
  • Well, I have implemented SAS DDS and I still hate the datatime values for temporal validity. Now, I'd simply store date values. I doubt you'll track intraday changes. – vasja Nov 15 '13 at 17:34
  • Not sure I agree, but nevertheless - which approach should be used? We have the same dilemma with date values! – Allan Bowe Nov 15 '13 at 17:45
  • 1
    For _all_ instances of 'imprecise' or variable precision types (essentially, anything except an integer count), please [read this blog post](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) (it's actually about timestamps, and somewhat specific to SQL Server). Personally, I think that `BETWEEN` should be removed from the standard, specifically _because_ of the "however" you list in your considerations; it simply encourages thinking about such ranges incorrectly... – Clockwork-Muse Nov 20 '13 at 12:22
  • 1
    ...Conceptually, the timing of the old record 'closing' isn't some time before the new record, it's the very act of making the new record that closes it (so, the exact instant). In fact, in a further normalized database, there wouldn't be a `TO_DTTM` column (stored) - it would be derived from the 'next' `FROM_DTTM` value (for performance considerations, this is usually an acceptable denormalization, however). – Clockwork-Muse Nov 20 '13 at 12:29
  • Not sure I agree that TO_DTTM can be dropped (at least - not without adding a DELETED flag for closed records), but I did read your blog post, and it definitely helps one to appreciate the merits of Approach 2. Many thanks! – Allan Bowe Nov 20 '13 at 13:53
  • Agreed with @damien_The_Unbeliever that Approach 1 is correct. Here is an article I wrote on this topic: https://datacontroller.io/bitemporal-historisation-and-the-sas-dds/ – Allan Bowe Aug 06 '20 at 21:51

1 Answers1

2

For continua (such as datetime, or numeric measures), I'd almost always recommend the semi-open interval approach (Approach 2). As you say, approach 1 tends to produce gaps or you need to know the exact precision to compute the end point. Calculating the end points in approach 2 always tends to be simpler.

The "benefit" of being able to use BETWEEN is, so far as I'm concerned, a very minor one compared with being able to quickly assure oneself of the correctness of the query.

For non-continuous data (dates without times, or discrete numerics) I might revert to using a closed interval.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • True.. data / query accuracy is more important than ease of syntax. Thanks! – Allan Bowe Nov 20 '13 at 11:40
  • 1
    After reviewing the document below on bi-temporal, it would seem that Approach 2 IS compatible with proposed 'AS OF TIMESTAMP' syntax. So even more reason to avoid Approach 1.. https://www.cs.arizona.edu/~rts/pubs/Teradatacasestudy.pdf – Allan Bowe Nov 21 '13 at 12:11
  • 1
    Ok - I think we have a definitive answer!! "The end-effective-datetime of a Type 2 dimension record must be exactly equal to the begin-effective-datetime of the next change for that dimension member." (http://www.kimballgroup.com/2008/09/22/slowly-changing-dimensions-part-2/) – Allan Bowe Nov 21 '13 at 12:48