Transact-SQL Cookbook

Chapter 5. Temporal Data

Almost any computer system uses some sort of temporal, or time-based, data. Temporal data could be in the form of contract dates, dates corresponding to school athletic results, or periods of time in which employees are booked in meetings. In any case, with the use of temporal data, database records have a given place on a timeline. However, a database can store only discrete time values, while time itself is continuous. This makes working with temporal data a bit of a challenge.

In this chapter, we’ll discuss the fundamentals of working with temporal data, and we’ll show how you can implement the three basic temporal types: instants, durations, and periods. We’ll also briefly show Transact-SQL functions that manipulate temporal data.

Our recipes demonstrate a mechanism for enforcing granularity rules, how to handle calendar information, and how to extend date datatypes beyond their default scope. We show techniques for manipulating instants, durations, and periods that can be used in any temporal-database problem.

The selection of recipes for this chapter reveals both the complexity of working with temporal data and also the range of possible solutions for temporal-data problems. Temporal data is not just the same as any other type of data. Temporal data has specific characteristics that can trip you up if you don’t fully understand what you are doing.

Introduction

Representing temporal data in a database is probably one of the most unnatural concepts for ...