Among search criteria, dates (and times) hold a particular place that is all their own. Dates are extremely common, and more likely than other types of data to be subjected to range conditions, whether they are bounded ("between this date and that date") or only partially bounded ("before this date"). Very often, and what this situation describes, the result set is derived from searches against date values referenced to the current date (e.g., "six months earlier than the current date," etc.).
The example in the previous section, "Result Set Obtained by
Aggregation," refers to a
sales_history table; our condition was on an
amount, but it is much more common with this type of table to have
conditions on date, especially to get a snapshot of the data either at a
given date or between two dates. When you are looking for a value on a
given date in a table containing historical data , you must pay particular attention to the way you
identify current data. The way you handle current data may happen to be
a special case of data predicated on an aggregate condition.
I have already pointed out in Chapter 1 that the design of a table destined to store historical data is a tricky affair and that there is no easy, ready-made solution. Much depends on what you plan to do with your data, whether you are primarily interested in current values or in values as of a particular date. The best solution also depends on how fast data becomes outdated. If you are ...