The woes of (sometimes) storing "date only" in datetimes
Posted
by Heinzi
on Stack Overflow
See other posts from Stack Overflow
or by Heinzi
Published on 2010-04-14T10:53:36Z
Indexed on
2010/04/14
11:03 UTC
Read the original article
Hit count: 471
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?
© Stack Overflow or respective owner