In a database there are two basic forms of time. The first is a point in time. This can be defined according to the smallest level of granularity available in your database engine. Often the nearest second is acceptable. In SQL Server, the single type DATETIME will round to the nearest 3 milliseconds. Using DATE and TIME you can be even more granular if it is needed.
Many things are defined by a point in time:
- Birthday
- Wedding Day
- Purchase Date of an Item
- Date you pay your taxes
Some people like to call this point in time a Timestamp in their database. In SQL Server this is not a common naming convention due to the fact that TIMESTAMP is a deprecated datatype. By practice, it is not a good thing to use a reserved word as the name for a column in a table. I tend to suffix a point in type column name with DTM, meaning Date and Time. That is a personal habit. I haven’t seen other developers using it.
The second type of data has been called a Time Segment. A time segment onsists of two points in time. The first point in time is when an entity becomes true. The second point in time is when an activity terminates. This is a very common, and natural way for tracking time for different things.
- Lifetime: Birth, Death
- Insurance: Coverage Starts, Coverage Ends
- Ownership: From, To
- Employment: Start, End
- Enrollment: Start, End
Time segments are a little tricky in that the Start Date is always know, but the End Date may be a continuing point in time. It changes with every tick of the clock. Sometimes it is simply left null, because it isn’t true until the actual termination event occurs. Other times, like with enrollment in insurance, the termination is set according to the duration for which you have currently paid. The termination date may be extended when another payment is made, or, a new time segment may be created.
Tomorrow we’ll take a look at some characteristics of these two different forms of tracking time.
Thanks for the comments to this point. As always, feel free to leave comments on this topic, asking questions, or leaving guidance or suggestions.
Cheers,
Ben