4 Approaches to Storing and Displaying Time Spans in Microsoft Access

Storing and displaying time spans is a deceptively difficult task.

By time spans, I don't mean separate start and end date fields.  I mean a single value that represents a length of time, such as:

• 15 seconds
• 3 minutes
• 2 hours
• 30 days
• 2 weeks
• 10 years

There are several ways to store that kind of data.  As with most data storage decisions, the correct answer ultimately boils down to, "It depends."  Any solution involves tradeoffs among several different factors, including:

• Flexibility: can it handle multiple units (seconds, days, months, years, etc.)?
• Simplicity: how difficult is it to implement?
• Speed: does it support efficient queries?
• Readability: does the stored data have meaning to humans or just computers?
• Data quality: does it help ensure clean data?
• Precision: how exact is the measurement (to the day, the second, the millisecond)?
• Capacity: what's the longest time span it supports?
• "Computability": can you use it in calculations (e.g., to calculate offsets from other date/time values)?
• Intuitiveness: how easy is it for the user to understand what to do?
• Efficiency: how efficient is it for the user to enter the information?

To demonstrate the challenges, let's consider a few possible solutions.

Approach 1. Stored as a String

Probably the simplest approach is to store the time span as a string.

Create a variable length text field in your table.  Slap a text box with no validation on your form.  Save whatever the user types in to the text box.  Display that data on reports in its original form.

It may sound like I'm knocking this approach, but depending on the situation, this may in fact be the best way to handle it.  This approach has several things going for it:

• PRO: flexibility, simplicity, speed, readability, precision, capacity
• CON: data quality, computability
• MIXED: intuitiveness, efficiency

Intuitiveness is mixed because users may or may not understand what is expected of them depending on the surrounding context.  Experienced users would be fine once properly trained on the system, but new users might enter time spans like, "short" or "long" instead of "2 weeks" or "5 years."

If you will NEVER be using the time span in a calculation–or relying on it actually being, you know, a time span–then this approach checks all the other boxes.  Of course, those two CONs are very big CONs in many situations.  And while you want to be wary of spending too much time planning for possible features in the future, taking this approach could severely limit your options down the line.

Approach 2. Stored as Individual Fields

With this approach, you would have separate fields for: seconds, minutes, hours, etc.

Create multiple integer fields in your database.  On your form, you would have individual bound text boxes for each unit of time you wanted to support.  You could include simple validation, such as disallowing negative amounts.

• PRO: flexibility, readability, data quality, precision, capacity, intuitiveness, computability
• CON: simplicity, speed, efficiency

Here's what a sample user interface might look like:

It's pretty intuitive and makes data validation straightforward, but it requires adding and maintaining multiple fields for a single piece of data.  

If you are looking for an easy to understand approach for collecting clean data–and don't mind maintaining the extra fields–this could be a good solution.

Approach 3. Stored as a Fractional Unit

With this approach, you would store the time span as a fraction of a year/day/hour/etc.

This is how VBA stores dates internally.  The number 1 would represent a single day, while 0.5 would represent 12 hours (i.e., half of a day):

The big challenge with this approach is how you collect the data from the user.  Asking the user to enter the fraction directly seems like a terrible user experience, unless you don't need to worry about time spans shorter than six hours.  

You could use the user interface from Approach 2.  The only difference would be that the text boxes could not be bound fields.  Instead, they would be unbound fields.  You would need to use a form event to combine the various values into a single fractional-day value that would get stored in the table.

There is also a challenge when displaying the data.  You could take a very simple approach and display values such as "14 days" or "0.5 days."  But, if you wanted to instead show values such as "2 weeks" or "8 hours," you would need a custom calculation to convert from days to those other formatted values.

Here's where things can get dicey.  Whenever you are multiplying and dividing to convert between floating point values, you run the risk of data loss.  There are some decimals that cannot be represented with binary floating point numbers.

• PRO: speed, data quality, capacity, computability
• CON: simplicity, readability, precision, intuitiveness, efficiency
• MIXED: flexibility

While this approach can be made to handle multiple units (seconds/hours/days), it requires extra work.  That's why I listed flexibility under the MIXED category.

I think I like this approach the least of all four.

Approach 4. Stored as a Whole Number Unit

With this approach, the data is stored as a long integer representing the number of seconds that constitute the time span.

This faces many of the same challenges as Approach 3, but benefits by not having to deal with the binary floating point arithmetic issues.

• PRO: speed, data quality, capacity, computability, precision
• CON: readability, intuitiveness, efficiency
• MIXED: simplicity, flexibility

The list above is the same as Approach 3, with two differences.

I moved "precision" from CON to PRO.  This reflects the inherently better precision of a whole number when compared to a fractional binary number.

I moved "simplicity" from CON to MIXED.  This reflects that it is relatively simpler to implement this approach than the one in Approach 3.

Implementation

In a future article*, I will write about my own implementation of the fourth approach.  

^{* If I forget to update this article with the link, search for "HumanizedSeconds" or "ConvertToSeconds."  Those are the names of the two functions I will be writing about.}

Image by Gerd Altmann from Pixabay