The woes of (sometimes) storing "date only" in datetimes

Question

We have two fields from and to (of type datetime), where the user can store the begin time and the end time of a business trip, e.g.:

From: 2010-04-14 09:00
  To: 2010-04-16 16:30

So, the duration of the trip is 2 days and 7.5 hours.

Often, the exact times are not known in advance, so the user enters the dates without a time:

From: 2010-04-14
  To: 2010-04-16

Internally, this is stored as 2010-04-14 00:00 and 2010-04-16 00:00, since that's what most modern class libraries (e.g. .net) and databases (e.g. SQL Server) do when you store a "date only" in a datetime structure. Usually, this makes perfect sense.

However, when entering 2010-04-16 as the to date, the user clearly did not mean 2010-04-16 00:00. Instead, the user meant 2010-04-16 24:00, i.e., calculating the duration of the trip should output 3 days, not 2 days.

I can think of a few (more or less ugly) workarounds for this problem (add "23:59" in the UI layer of the to field if the user did not enter a time component; add a special "dates are full days" Boolean field; store "2010-04-17 00:00" in the DB but display "2010-04-16 24:00" to the user if the time component is "00:00"; ...), all having advantages and disadvantages. Since I assume that this is a fairly common problem, I was wondering:

• Is there a "standard" best-practice way of solving it?
• If there isn't, have you experienced a similar requirement, how did you solve it and what were the pros/cons of that solution?

Answer 1

There isn't a magic way to do this that significantly improves on storing 23:59 (or whatever level of accuracy you prefer) in the 'to' field.

This type of requirement pops up in insurance policy administration systems used within U.S. jurisdictions. Often, inception and expiry dates have to be recorded with the time, and expiry dates are recorded as xxxx-xx-xx 23:59. Sadly, there's no magic formula for this, other than defaulting the time component on to: fields to 23:59.

Answer 2

The best way IMO is to use a platform or class library which does have the concept of "a date" separate from "a date and time" and the many variations on the theme. (Don't forget the possibility that between "from" and "to" there could be a daylight saving transition :)

In Java I'd suggest using Joda Time; in .NET my Noda Time project will hopefully be the best solution when it's ready.

So in this case you really do need to know whether a date was entered or midnight - they're fundamentally different. Quite what you do if they enter "from" as a date and "to" as a date/time, I'm not sure... probably still use "start of day" for the "from" and the given time for the "to"... but it's a bit dodgy.

Fundamentally though, you should try to store all the information you know and only the information you know - so if you don't have separate types, storing a separate "this is just a date" field seems reasonable. It's ugly, but that's because the platform isn't helping you as much as it should.