Date ranges: inclusive vs strict boundaries

Question

When allowing a user to select a date range, let's say:

Show me entries from [August 1] to [September 1]

As a user, I would generally expect this to include the results for September 1. Especially when you consider that when I select the same date for both ends, I obviously mean "from start of day to end of day":

Show me entries from [September 1] to [September 1]

As a programmer, I think of date boundaries as "zero-hour", i.e. "start-of-day"; logically, the entries for September 1 are actually after "2010-09-01 00:00:00" (hence outside of the range).

For example, in SQL the following condition would exclude everything:

SELECT * FROM entries
WHERE created_at >= DATE('2010-09-01') AND created_at <= DATE('2010-09-01')

Obviously, an adjustment needs to be made from the user input to the SQL to advance the end date by 24 hours.

However, that only applies to timestamp or datetime columns. When the column is a date then a direct comparison works and this adjustment should not be added.

In an MVC framework such as Rails, where do you handle the logic for this input mismatch before sending a query? If it's in the controller, it seems that relies too much on knowing the internal fields of the model (date vs. datetime), and if it's in the model, would a "find_in_date_range" method be understood as inclusive, or does that just invite off-by-day errors?

Finally, is my assumption correct for having the user interface represent inclusive ranges? Is this always the case or are there situations where a strict (exclusive) date boundary is more appropriate? For example, in my rake scripts I use the parameter END_DATE=2010-09-01 to capture up to this date, which is inconsistent with the UI, but makes sense to me: where do you draw this line?

Answer 1

My Personal Bias:

Program it however you think the users will like it, but ALWAYS make the inclusive vs exclusive choice explicit in the GUI with labeling. Rather than saying

Between _____________ and ______________

I always label as

On or after _______________ but before _______________

or

On or after ______________ through ______________

(If you are writing software that a few users use every day and you can carefully train them, then they aren't going to be reading the GUI labels anyway, so don't bother.)