Yesterday we considered two different kinds of time. A point in time is an event that occurs and is completed immediately. A time segment is an event that begins at a specific point in time, and ends at another, later point in time. The later point in time for a time segment may not have yet occurred.
Time becomes more complicated when you compare one time entity to another. For example, you may be enrolled in an insurance program starting in January and ending in June. The Insurance company may produce a report for monthly enrollment for the year. Your enrollment would be included in the count for the months of January through June. Others customers may have different enrollment periods.
Calculating the enrollment per month means you must include all enrollments that are active for any period between the beginning and ending of the month. Some time/relational gurus would have you de-compose both time segments.
The reporting time segment for January would de-compose into 31 days for the month. The individual enrollment would decompose into every day from the beginning of January to the end of June. Once decomposed, you can find the intersection of the two simply by finding if either entity had a corresponding day, regardless of which day. This implementation is often used in a data warehouse where multiple time dimensions may be associated with an entity. They are calculated when the warehouse is created, not at run time.
Another approach is simply to compare two time segments. Four tests are necessary:
- Segment A Start is between Segment B Start and End
- Segment A End is between Segment B Start and End
- Segment B Start is between Segment A Start and End
- Segment B End is between Segment A Start and End
If any of the above results in true, then the time segments overlap, and there is a virtual intersection of the two sets.
Point in time comparisons are obviously much easier. Comparing to another point in time, they must equal each other. Comparing to a point in time value to a time segment, the point in time must be between the segment start and end periods. No real surprise there.
All of this theory is common sense. If you know that an Event occurs, and does not continue, then a point in time will most likely do. If it occurs, and may have a termination point in time different than the origination point in time, you probably have a time segment. Even if an event may be continuing, sometimes you may choose to record it as a point in time, because only the origination is relevant to your system.
Here’s an interesting scenario. The owner of the insurance agency wants to know how many new policies were originated for each month in the year. In this case you only need compare the start date of the policy for falling between the start and end period of each month. Instead of four tests as demonstrated above, only one single test is required.
Now I’m out of time for writing this editorial. As always, please leave comments with your perspective.
Cheers,
Ben