Dates are points on time scales we know as calendars, and times are points on scales we view as clocks. From a well-defined data perspective they are actually quantities, similar to those described in Part 7. Like other quantity attributes, points in time have units of measure, precision, and their values can participate in calculations.
A date, time, or combined date/time attribute represents when something occurred (or will occur). There are business entities, such as purchase, flight, and journal entry, which represent events of interest to an organization. These entities act as the context for one or more point-in-time attributes. Flights, for example, will have a number of point-in-time attributes, including ‘scheduled departure date/time’ and ‘scheduled arrival date/time'.
There are business entities such as customer, product, and location, which are not events themselves but can act as the context for events related to them. A customer that is an individual can have a ‘date of birth’. A product can have a ‘launch date’. A location can have daily opening and closing times. Each of these points-in-time attributes represent the ‘when’ aspect of an event of interest to the organizationn.
Point-in-time Units of Measure
The most common calendar system in civil use is the Gregorian calendar, with its year zero some 2000 years ago and its units of years, subdivided into 12 months which in turn are divided into 28 to 31 days. Google advises that there are some 40 other calendar systems used around the world, most associated with a religion. Each has its designated year of origin, sub-units of months, and days within months.
The commonly-recognized time scale divides a day into 24 hours with sub-units of minutes and seconds. The zero-point of a day is most often assumed to be midnight local time. Organizations that involve events that can take place in different time zones require an additional unit of measure attribute whose role is to identify the time point’s specific time zone. Again, see Part 7 for its discussion regarding associating units of measure with quantity attributes.
Different organizations or industries can have different precision requirements for the same event. For example, a person’s birth event. ‘Day’ is the most common precision. However, organizations that deal with official birth records record the event date/time to the nearest ‘minute’. Conversely, only the year of birth for an author is needed by book publishers, book retailers, and libraries — used to distinguish between authors with the same (or similar) names.
If the point-in-time attribute involves precision finer than ‘day’, the options progress from hours, to minutes, to seconds, and finally some number of decimals of seconds. The nearest ‘minute’ is usually sufficient for human-related activities. When greater precision is needed, microchip-based timekeeping devices are utilized to source a value (e.g. a POS terminal recording the time-of-purchase transaction to the second).
The easiest way to think of ‘date’ or ‘time’ precision is digitally. Think of a digital clock that displays both the date and time, with a 4-digit year and 2-digit months and days. A digital clock (and point-in-time attributes) have no concept of precision-based ‘rounding’ (e.g. to the nearest year, month, or day). The same applies to point-in-time precision for ‘hour’, ‘minute’, or ‘second’ values.
Period-defining Time Point Attributes
Business time periods can be specified in one of three ways:
- Two different point-in-time attributes — one marking the start and the other marking the end.
- One point-in-time attribute and a quantity attribute representing the duration.
- A sequence of time-point attributes indicating a start point, and the subsequent start point implying the end point of the previous period.
Two different Points — Two different point-in-time attributes are defined, both with the same units of measure and precision. Naming and/or definition should make the role of each clear (start or end marker) and that the two attributes are associated. Business rules need to be confirmed indicating if two or more periods are allowed to overlap and if gaps in time are allowed between periods. E.g. the event-entity ‘staff assignment’ can have overlapping periods indicating job sharing and gaps indicating vacancy periods.
Time Point and Duration — Given either a start or end point in time, plus a duration, the other time point can be derived. For example, given a ‘contract’ start date and a contract duration of four weeks, the end date can be calculated.
Sequential Points in Time — In cases where time periods are contiguous (i.e. no overlapping and no gaps), only one time point attribute is needed. For example, foreign exchange rates. At the point in time a new rate value becomes effective, the previous value ceases to be in effect. Whether to define the start point and assume the end point or the reverse depends on which best represents the business event. In the exchange rate example, there being a new rate taking effect clearly is the event.
Well-defined Point-in-time Attributes
From a data dictionary template perspective, a well-defined point-in-time attribute should have the following properties addressed:
- Name — Follow any organizational standards for naming date, time, and date/time attributes. Where one of a pair of attributes specifies a time period, try to be consistent with pair names (e.g. begin/end, start/stop, from/to, or effective/expiry)
- Definition — Describe the business event that time point is marking. Include examples that illustrate precision and any rules that may apply.
- Unit(s) of measure — Specify calendar system if other than Gregorian (or explain in Definition). For Time, specify ‘local time’, or indicate the attribute that identifies the associated time zone.
- Precision — The precision that is required for business purposes. Normal dates can assume ‘day’ as the precision. Time (or Date/Time) captured would not be more precise than minutes.
- Associated-Period Boundary Attribute — Where the time point is one of a from/to pair, identify the other member of the pair.
- Future values allowed (Y/N)?
- Historic values allowed (Y/N)?
- Derivation — For a date or time being derived, a business definition or rule describing the derivation (e.g. ‘Best Before’ date derived from Product Batch ‘Processed Date’ plus Product Type’s ‘Shelf Life Days’). Include examples using business values.
- Validation — Reference business rules or describe. E.g. Value should not be more than 50 years beyond current date.
Coming next – 5 Questions for Business Stakeholders About Their Data Requirements
The remaining topics applicable to well-defined data are wrapped up in next (and final) article in this series. The topics are addressed in the form of questions, such as ‘optional or mandatory’, that require responses from business stakeholders. The questions, and their answers, are applicable to either attributes or relationships.