The woes of (sometimes) storing "date only" in datetimes
- by Heinzi
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?
