How do you store Date ranges, which are actually timestamps

Question

Java & Oracle both have a timestamp type called Date. Developers tend to manipulate these as if they were calendar dates, which I've seen cause nasty one-off bugs.

1. For a basic date quantity you can simply chop off the time portion upon input, i.e., reduce the precision. But if you do that with a date range, (e.g.: 9/29-9/30), the difference between these two values is 1 day, rather than 2. Also, range comparisons require either 1) a truncate operation: start < trunc(now) <= end, or 2) arithmetic: start < now < (end + 24hrs). Not horrible, but not DRY.

2. An alternative is to use true timestamps: 9/29 00:00:00 - 10/1 00:00:00. (midnight-to-midnight, so does not include any part of Oct). Now durations are intrinsically correct, and range comparisons are simpler: start <= now < end. Certainly cleaner for internal processing, however end dates do need to be converted upon initial input (+1), and for output (-1), presuming a calendar date metaphor at the user level.

How do you handle date ranges on your project? Are there other alternatives? I am particularly interested in how you handle this on both the Java and the Oracle sides of the equation.

Answer 1

Here's how we do it.

1. Use timestamps.

2. Use Half-open intervals for comparison: start <= now < end.

Ignore the whiners who insist that BETWEEN is somehow essential to successful SQL.

With this a series of date ranges is really easy to audit. The database value for 9/30 to 10/1 encompass one day (9/30). The next interval's start must equal the previous interval's end. That interval[n-1].end == interval[n].start rule is handy for audit.

When you display, if you want, you can display the formatted start and end-1. Turns out, you can educate people to understand that the "end" is actually the first day the rule is no longer true. So "9/30 to 10/1" means "valid starting 9/30, no longer valid starting 10/1".

Answer 2

Oracle has the TIMESTAMP datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, second and fractional second values.

Here is a thread on asktom.oracle.com about date arithmetic.