The trouble of (sometimes) storing "dates only" in dates
We have two fields from
and to
(types datetime
) where the user can save the start time and end time of the trip, for example:
From: 2010-04-14 09:00
To: 2010-04-16 16:30
Thus, the duration of the trip is 2 days and 7.5 hours.
Often the exact time is not known in advance, so the user enters dates without any 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
as this is what most modern class libraries (eg..net) and databases (eg SQL Server) do when you store "date only" in a datetime structure. This usually makes sense.
However, when entered 2010-04-16
as a date, the to
user clearly did not mean it 2010-04-16 00:00
. Instead, the user meant 2010-04-16 24:00
, that is, the calculation of the duration of the shutdown should output 3 days
, not 2 days
.
I can think of a few (more or less ugly) workarounds for this problem (add "23:59" to the UI layer of the field to
, if the user has not entered a time component, add the special "dates - full days" Boolean field, store "2010- 04-17 00:00 "in DB, but displaying" 2010-04-16 24:00 "to the user if the time component is" 00:00 "; ...), all of which have advantages and disadvantages. Since I assume this is a fairly common problem, I was wondering:
- Is there a "standard" best way to solve the problem?
- If this did not happen, you had a similar requirement, how did you solve it and what were the pros and cons of this solution?
a source to share
There is no magic way to do this, which will greatly improve saving 23:59 (or whatever level of precision you prefer) to the To field.
This type of claim appears in the policy administration systems used in US jurisdictions. Often times, the start and end dates of the recording must be recorded with time, and the expiration dates are recorded as xxxx-xx-xx 23:59. Unfortunately, there is no magic formula for this purpose, other than using the default time component: fields until 23:59.
a source to share
The best way IMO is to use a framework or class library that has the concept of "date" separate from "date and time" and many variations of the theme. (Do not forget that there may be daylight saving time between "from" and "to" :)
In Java, I would suggest using Joda Time ; in .NET my Project Noda Time will hopefully be the best solution when it's ready.
So, in this case, you really need to know if a date or midnight was entered - they are fundamentally different. Quite what you do if they enter "from" as date and "to" as date / time, I'm not sure ... maybe still use "start of day" for "from" and given time for "to" "... but it's a little quirky.
Basically, you should try to store all the information you know and only the information you know, so if you don't have separate types, keeping a separate "it's just a date" field seems reasonable. It's ugly, but that's because the platform doesn't help you as much as it needs to.
a source to share