|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Storing DateTime Values - Sometimes with Time, Sometimes Without TimeWhat is a standard or acceptable practice for storing datetime values in a
column for which 1. the date is always known 2. the time may not be known (say 40% of rows do not have a time value). Specific question: What value is to be stored for the time value [of the datetime column] when the time values is not known? Do we just store "zero" and have the consumer interpret that as meaning "unknown"? I have seen some databases with two columns - one for the date, the other for the time - but I don't want to have two columns (it just seems wrong). Thanks! I don't know that there is a standard or accepted practice, I've never
encountered that situation myself. The real question is what is your business requirement. Does the business need to differentiate between, say, an actual time of exactly 00:00:00.000 and a time that is missing? If so, you need two columns, in some form. Otherwise you can probably get away with just setting the time to zeroes. Too bad Microsoft hasn't had the sense to provide proper date and time datatypes. Back when 6.0 and new and 6.5 being written I thought I had Ron Soukup convinced of that need, but it never paid off. Roy Harvey Beacon Falls, CT Show quote On Thu, 13 Jul 2006 09:25:52 -0700, "Jeremy" <A@B.COM> wrote: >What is a standard or acceptable practice for storing datetime values in a >column for which >1. the date is always known >2. the time may not be known (say 40% of rows do not have a time value). > >Specific question: What value is to be stored for the time value [of the >datetime column] when the time values is not known? Do we just store "zero" >and have the consumer interpret that as meaning "unknown"? > >I have seen some databases with two columns - one for the date, the other >for the time - but I don't want to have two columns (it just seems wrong). > >Thanks! > Roy Harvey wrote:
> I don't know that there is a standard or accepted practice, I've never Aren't you dating yourself with that last statement? :-)> encountered that situation myself. > > The real question is what is your business requirement. Does the > business need to differentiate between, say, an actual time of exactly > 00:00:00.000 and a time that is missing? If so, you need two columns, > in some form. Otherwise you can probably get away with just setting > the time to zeroes. > > Too bad Microsoft hasn't had the sense to provide proper date and time > datatypes. Back when 6.0 and new and 6.5 being written I thought I > had Ron Soukup convinced of that need, but it never paid off. > On Thu, 13 Jul 2006 12:28:54 -0500, Tracy McKibben
<tr***@realsqlguy.com> wrote: >Roy Harvey wrote: I prefer to think of it as reflecting the depth of my experience. 8-)>> Too bad Microsoft hasn't had the sense to provide proper date and time >> datatypes. Back when 6.0 and new and 6.5 being written I thought I >> had Ron Soukup convinced of that need, but it never paid off. >> > >Aren't you dating yourself with that last statement? :-) Roy << I prefer to think of it as reflecting the depth of my experience. 8-) >>
Oh, so you're a dinosaur Show quote :-) Careful now...
-- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Jeremy" <A@B.COM> wrote in message news:e6MrGSqpGHA.1600@TK2MSFTNGP04.phx.gbl... > << I prefer to think of it as reflecting the depth of my experience. 8-) > >> > > Oh, so you're a dinosaur > > :-) > A date with a zero time component will always be exactly midnight
[00:00:00.000]. If the time value is of little consequence, that is probably adequate. But if, like Roy indicates, the there is a need to discriminate between [NO TIME] vs. [Midnight], then you must use a second column to handle time. (The time portion of the date column will always be midnight, and the date portion of the time column will always be '01/10/1900'. (Unless null of course.) Or a second 'flag' column to indicate that the time portion of datetime is NOT to be ignored -even if midnight. I hope that this helps. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Roy Harvey" <roy_har***@snet.net> wrote in message news:g1ucb2dpn1vsmqt2o7ocukb0k4sv8dovag@4ax.com... >I don't know that there is a standard or accepted practice, I've never > encountered that situation myself. > > The real question is what is your business requirement. Does the > business need to differentiate between, say, an actual time of exactly > 00:00:00.000 and a time that is missing? If so, you need two columns, > in some form. Otherwise you can probably get away with just setting > the time to zeroes. > > Too bad Microsoft hasn't had the sense to provide proper date and time > datatypes. Back when 6.0 and new and 6.5 being written I thought I > had Ron Soukup convinced of that need, but it never paid off. > > Roy Harvey > Beacon Falls, CT > > On Thu, 13 Jul 2006 09:25:52 -0700, "Jeremy" <A@B.COM> wrote: > >>What is a standard or acceptable practice for storing datetime values in a >>column for which >>1. the date is always known >>2. the time may not be known (say 40% of rows do not have a time value). >> >>Specific question: What value is to be stored for the time value [of the >>datetime column] when the time values is not known? Do we just store >>"zero" >>and have the consumer interpret that as meaning "unknown"? >> >>I have seen some databases with two columns - one for the date, the other >>for the time - but I don't want to have two columns (it just seems wrong). >> >>Thanks! >> >> What is a standard or acceptable practice for storing datetime values in a column for which 1. the date is always known2. the time may not be known (say 40% of rows do not have a time value). << First things first. If you have temporal DB questions, you go to Rick Snodgrass at the University of Arizona and download his classic book as a PDF. Next, read Zeno's paradoxes. Time is a continuum so you need to model it as a pair of (start_time, end_time) -- the 4-th dimension's version of (x,y) co-ordinates. When you have an exact time to the finest grandularity of your system, then the two values are equal; when you do not know the event down to "a super-nano-picto-atto -second", then use 00:00:00 to 23:59:59:999... for the time component.
Show quote
On 13 Jul 2006 11:17:27 -0700, "--CELKO--" <jcelko***@earthlink.net> And then, having suitably armed yourself, go back to the users andwrote: >>> What is a standard or acceptable practice for storing datetime values in a column for which > 1. the date is always known > 2. the time may not be known (say 40% of rows do not have a time >value). << > >First things first. If you have temporal DB questions, you go to Rick >Snodgrass at the University of Arizona and download his classic book as >a PDF. > >Next, read Zeno's paradoxes. Time is a continuum so you need to model >it as a pair of (start_time, end_time) -- the 4-th dimension's version >of (x,y) co-ordinates. When you have an exact time to the finest >grandularity of your system, then the two values are equal; when you do >not know the event down to "a super-nano-picto-atto -second", then use >00:00:00 to 23:59:59:999... for the time component. find out what the business needs. Implement that. Roy I think the references Joe gives here will only disarm us.
http://www.amazon.com/gp/product/0486296644/ Zeno's paradox is a fallacy or an illusion -- it exemplifies reductio ad absurdum. For all modeling purposes all times are discrete. If one wants to embrace Zeno's paradox as a fact, then he must have to embrace the absurdity of modeling distance and space as continua as well. -- Anith >> Zeno's paradox is a fallacy or an illusion -- it exemplifies reductio ad absurdum. For all modeling purposes all times are discrete. << NO! NO! NO! Zeno is not so much a fallacy, but rather a bad question.Cantor and all that jazz? See also "Hilbert's Hotel" and other stuff. Remember those classes in your Master degree in Math on Sets and Foundations? What the "Zeno's arrow" et al demonstrate is that a continuum (c or Aleph One, depending on your axioms) is not like countable sets of elements (Aleph null) -- this is the whole point of Cantor's Alephs. The Greeks had geometry but they did not make the next step to a continuum. The ANSI/ISO model ihas moved to the Snodgrass model of (start_time, finish_time_or open) mode; of time, which is whart Snodgrass advocated a few decades ago when I was on ANSI X3H2. >> NO! NO! NO! Zeno is not so much a fallacy, but rather a bad question. Bad question? http://en.wikipedia.org/wiki/Zeno's_paradoxes>> See also "Hilbert's Hotel" and other stuff. Remember those classes in What about them? Hilbert's paradox simply amplifies the property of infinite >> your Master degree in Math on Sets and Foundations? sets, essentially emphasizing equivalence to its infinite subset. Unless we construct the infinite computer, the point is modeling infinity lacks precision to the point where it is almost impossible. >> What the "Zeno's arrow" et al demonstrate is that a continuum (c or Aleph So if you are buying into Zeno's arrow paradox, why not accept the myth of >> One, depending on your axioms) is not like countable sets of elements >> (Aleph null) -- this is the whole point of Cantor's Alephs. The Greeks >> had geometry but they did not make the next step to a continuum. Achilles never catching up with the tortoise and model all your lengths and distances as pairs of points? >> The ANSI/ISO model ihas moved to the Snodgrass model of (start_time, The last time I read Snodgrass, he had clearly defined instants and >> finish_time_or open) mode; of time, which is whart Snodgrass advocated a >> few decades ago when I was on ANSI X3H2. intervals both of which applied to finite sets of values. -- Anith >> And then, having suitably armed yourself, go back to the users and find out what the business needs. Implement that. << Wants or needs? Sorry, I adopted teenage girls late in my life. Youhave no idea what a . "demanding user" really is :) Find the **Minimum** needs of the users, then implement a flexlible, scalable data model.that gives them room. Youn are the doctor, not Rush Limbaugh's pill puisher. Lets say we're storing facts about a patient in a medical clinic in 3
tables: ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. PatientsXThingsWeMeasure looks something like this: PatientID int NOT NULL MeasurementID int NOT NULL WhenWeMeasuredIT datetime NOT NULL Value varchar(10) NOT NULL And we store measurements of things in it like pulse, blood pressure, and head circumference (and hundreds of other measurements - [no exaggeration]). The users of this system do not care about the *time* the head circumference was measured. It's measured once per week and the time of day is unimportant to the business. Blood pressure and pulse may be measured multiple times per day - so time is important. Yes, we could say, "okay it's unimportant to record the time of measurement for somethings - but make 'em put it in anyway." Great then what about all the legacy data that is missing the time value for many of these measurements? I have to import a bunch of data from an old database that doesn't store the time of many measurements. What to do??? In the ThingsWeMeasure table - I assume you would have one - I might
add an attribute TimeSignificance (or some better name) with as few possible values as possible, hopefully just two representing date or datetime. That would be available when analyzing the data to tell you if the time should be ignored for a specific ThingsWeMeasure. Roy Show quote On Thu, 13 Jul 2006 14:47:29 -0700, "Jeremy" <A@B.COM> wrote: >Lets say we're storing facts about a patient in a medical clinic in 3 >tables: ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. > >PatientsXThingsWeMeasure looks something like this: >PatientID int NOT NULL >MeasurementID int NOT NULL >WhenWeMeasuredIT datetime NOT NULL >Value varchar(10) NOT NULL > >And we store measurements of things in it like pulse, blood pressure, and >head circumference (and hundreds of other measurements - [no exaggeration]). > >The users of this system do not care about the *time* the head circumference >was measured. It's measured once per week and the time of day is unimportant >to the business. Blood pressure and pulse may be measured multiple times per >day - so time is important. Yes, we could say, "okay it's unimportant to >record the time of measurement for somethings - but make 'em put it in >anyway." Great then what about all the legacy data that is missing the time >value for many of these measurements? I have to import a bunch of data from >an old database that doesn't store the time of many measurements. > >What to do???  |
|||||||||||||||||||||||
Other interesting topics