|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DateTime Question (Again)Values - Sometimes with time, Sometimes without. If you review that thread you'll see how quickly it got WAY off topic. So I hope it's not considered rude for me to repost the same question again within 24 hours. I really need some relevant suggestions... thus this new/recycled post. My situation is that I have a "real world" mess on my hands. To summarize, here is the short version of this difficult situation: 1. We must store values for measurements; some of which the business does not care about the time of day, and some of which they do care about the time of day; and all of which they do care about the date the measure was taken. 2. There is a lot of historical data (13+ years worth) of which a large percentage is missing time of day. The client is a medical clinic associated with a university medical center and they want to use the data for research purposes. And yes, they understand AND have documented the flaws accumulated in the data over more than a couple of other database implementations over the years (and now we're migrating it into yet another and hopefully better model). While there are some problems, they are well-known AND there is still a huge amount of valid and reliable data. We're trying to do better for the future while keeping the extensive historical data available in the same database. 3. There are [up to] hundreds of things measured about each patient. Some measures are recorded quantitatively and others qualitatively. Some "things we measure" have one result (patient's pulse) while other things have two results (blood pressure has systolic and diastolic values). 4. There could reasonably be many NULLs for any given set of measurements for any given patient. Men don't have any measures collected related to various pregnancy-related factors. Some people don't smoke. Head circumference is measured over time for infants, but is not measured for adults, and on and on and on. Some people have no known allergic reactions to certain medications - thus no list of medications they are allergic to. That should give you a pretty good idea of the challenges being faced. Rather than asking you to solve all of my problems, I'm simply seeking guidance on how to store date and time values when the time value is not always known or cared about. Because there are up to hundreds of things being measured, we don't want to store the data in columns. Rather we are likely going to define 3 tables. We have not created them yet, but they would be something like this: ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. PatientsXThingsWeMeasure would look something like this: PatientID int NOT NULL MeasurementID int NOT NULL WhenWeMeasuredIT datetime NOT NULL Value varchar(10) NOT NULL So my specific question is do we keep WhenWeMeasuredIT as it is? Or should we split the date and time columns out, with a "TimeWeMeasuredIt" column that is nullable? Thoughts, opinions, perspective? Thanks! Jeremy wrote:
> Rather than asking you to solve all of my problems, I'm simply seeking I didn't read the original thread, but....> guidance on how to store date and time values when the time value is not > always known or cared about. The standard way is use a datetime column to store both date and time. If time is unknown or not applicable, then it will be stored as 00:00:00 (i.e. midnight). In your case, since you have a variable quality of data, it means that a time of '00:00:00' may mean it wasn't captured, OR that the event actually occurred exactly at midnight. Chris Jeremy
It is ok to store datetime column i mean the date along with time, so you can easily to extract a time portion as you need BTW , it will be a great improvment if MS will providfe DATE and TIME datatypes in the futer versions as well as an ability to put a table name as a paremeter to a stored procedure , however i can only WISH Show quote "Jeremy" <A@B.COM> wrote in message news:u%23Xq0EwpGHA.4932@TK2MSFTNGP05.phx.gbl... >I posted a question earlier today (9:25 AM). Subject: Storing DateTime >Values - Sometimes with time, Sometimes without. > > If you review that thread you'll see how quickly it got WAY off topic. So > I hope it's not considered rude for me to repost the same question again > within 24 hours. I really need some relevant suggestions... thus this > new/recycled post. > > My situation is that I have a "real world" mess on my hands. To summarize, > here is the short version of this difficult situation: > 1. We must store values for measurements; some of which the business does > not care about the time of day, and some of which they do care about the > time of day; and all of which they do care about the date the measure was > taken. > > 2. There is a lot of historical data (13+ years worth) of which a large > percentage is missing time of day. The client is a medical clinic > associated with a university medical center and they want to use the data > for research purposes. And yes, they understand AND have documented the > flaws accumulated in the data over more than a couple of other database > implementations over the years (and now we're migrating it into yet > another and hopefully better model). While there are some problems, they > are well-known AND there is still a huge amount of valid and reliable > data. We're trying to do better for the future while keeping the extensive > historical data available in the same database. > > 3. There are [up to] hundreds of things measured about each patient. Some > measures are recorded quantitatively and others qualitatively. Some > "things we measure" have one result (patient's pulse) while other things > have two results (blood pressure has systolic and diastolic values). > > 4. There could reasonably be many NULLs for any given set of measurements > for any given patient. Men don't have any measures collected related to > various pregnancy-related factors. Some people don't smoke. Head > circumference is measured over time for infants, but is not measured for > adults, and on and on and on. Some people have no known allergic reactions > to certain medications - thus no list of medications they are allergic to. > > That should give you a pretty good idea of the challenges being faced. > > Rather than asking you to solve all of my problems, I'm simply seeking > guidance on how to store date and time values when the time value is not > always known or cared about. > > Because there are up to hundreds of things being measured, we don't want > to store the data in columns. Rather we are likely going to define 3 > tables. We have not created them yet, but they would be something like > this: > > ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. > > PatientsXThingsWeMeasure would look something like this: > PatientID int NOT NULL > MeasurementID int NOT NULL > WhenWeMeasuredIT datetime NOT NULL > Value varchar(10) NOT NULL > > So my specific question is do we keep WhenWeMeasuredIT as it is? Or should > we split the date and time columns out, with a "TimeWeMeasuredIt" column > that is nullable? > > Thoughts, opinions, perspective? > > Thanks! > As was indicated in the earlier thread. Separate columns are ONLY needed if
you need to be able to discriminate between exactly midnight (00:00:00.000 -to the millisecond) as a 'actual' time or 'no time indicated'. If not, then a single column seems quite adequate. If you need to know if something happened at exactly 59:59:59.997, or 00:00:00.000, or 00:00:00.003 (as opposed to 'unknown'), then you must have two columns. The second column allows a null value for unknown time. But really, while it wasn't answering your question, wasn't some of that disgression interesting? -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Jeremy" <A@B.COM> wrote in message news:u%23Xq0EwpGHA.4932@TK2MSFTNGP05.phx.gbl... >I posted a question earlier today (9:25 AM). Subject: Storing DateTime >Values - Sometimes with time, Sometimes without. > > If you review that thread you'll see how quickly it got WAY off topic. So > I hope it's not considered rude for me to repost the same question again > within 24 hours. I really need some relevant suggestions... thus this > new/recycled post. > > My situation is that I have a "real world" mess on my hands. To summarize, > here is the short version of this difficult situation: > 1. We must store values for measurements; some of which the business does > not care about the time of day, and some of which they do care about the > time of day; and all of which they do care about the date the measure was > taken. > > 2. There is a lot of historical data (13+ years worth) of which a large > percentage is missing time of day. The client is a medical clinic > associated with a university medical center and they want to use the data > for research purposes. And yes, they understand AND have documented the > flaws accumulated in the data over more than a couple of other database > implementations over the years (and now we're migrating it into yet > another and hopefully better model). While there are some problems, they > are well-known AND there is still a huge amount of valid and reliable > data. We're trying to do better for the future while keeping the extensive > historical data available in the same database. > > 3. There are [up to] hundreds of things measured about each patient. Some > measures are recorded quantitatively and others qualitatively. Some > "things we measure" have one result (patient's pulse) while other things > have two results (blood pressure has systolic and diastolic values). > > 4. There could reasonably be many NULLs for any given set of measurements > for any given patient. Men don't have any measures collected related to > various pregnancy-related factors. Some people don't smoke. Head > circumference is measured over time for infants, but is not measured for > adults, and on and on and on. Some people have no known allergic reactions > to certain medications - thus no list of medications they are allergic to. > > That should give you a pretty good idea of the challenges being faced. > > Rather than asking you to solve all of my problems, I'm simply seeking > guidance on how to store date and time values when the time value is not > always known or cared about. > > Because there are up to hundreds of things being measured, we don't want > to store the data in columns. Rather we are likely going to define 3 > tables. We have not created them yet, but they would be something like > this: > > ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. > > PatientsXThingsWeMeasure would look something like this: > PatientID int NOT NULL > MeasurementID int NOT NULL > WhenWeMeasuredIT datetime NOT NULL > Value varchar(10) NOT NULL > > So my specific question is do we keep WhenWeMeasuredIT as it is? Or should > we split the date and time columns out, with a "TimeWeMeasuredIt" column > that is nullable? > > Thoughts, opinions, perspective? > > Thanks! > Needing to distinguish between time 00:00:00 meaning midnight or no value for
time is a good reason to split it out.. But there is another good reason, and that is trending and time based reporting... If you wish to see trends by time, or need to report aggregate values hourly across multiple days, splitting out time is a great idea. The only question becomes whether or not you LEAVE the time value in the date time field. Consider the value in reporting from a single datetime column vs concatenating the date part of the datetime with the time column. The other thing you consider is that there is some value to leaving all of the times to midnight, then your queries on this datetime column will always, only refer to days, making some queries easier to do. If you decide to have a time column ( and I think I would do it in your case.) decide the grain, or how fine the time you need should be, then store it as an ingeter..(ie hhmmss - 1am would be 10000) -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Jeremy" wrote: > I posted a question earlier today (9:25 AM). Subject: Storing DateTime > Values - Sometimes with time, Sometimes without. > > If you review that thread you'll see how quickly it got WAY off topic. So I > hope it's not considered rude for me to repost the same question again > within 24 hours. I really need some relevant suggestions... thus this > new/recycled post. > > My situation is that I have a "real world" mess on my hands. To summarize, > here is the short version of this difficult situation: > 1. We must store values for measurements; some of which the business does > not care about the time of day, and some of which they do care about the > time of day; and all of which they do care about the date the measure was > taken. > > 2. There is a lot of historical data (13+ years worth) of which a large > percentage is missing time of day. The client is a medical clinic associated > with a university medical center and they want to use the data for research > purposes. And yes, they understand AND have documented the flaws accumulated > in the data over more than a couple of other database implementations over > the years (and now we're migrating it into yet another and hopefully better > model). While there are some problems, they are well-known AND there is > still a huge amount of valid and reliable data. We're trying to do better > for the future while keeping the extensive historical data available in the > same database. > > 3. There are [up to] hundreds of things measured about each patient. Some > measures are recorded quantitatively and others qualitatively. Some "things > we measure" have one result (patient's pulse) while other things have two > results (blood pressure has systolic and diastolic values). > > 4. There could reasonably be many NULLs for any given set of measurements > for any given patient. Men don't have any measures collected related to > various pregnancy-related factors. Some people don't smoke. Head > circumference is measured over time for infants, but is not measured for > adults, and on and on and on. Some people have no known allergic reactions > to certain medications - thus no list of medications they are allergic to. > > That should give you a pretty good idea of the challenges being faced. > > Rather than asking you to solve all of my problems, I'm simply seeking > guidance on how to store date and time values when the time value is not > always known or cared about. > > Because there are up to hundreds of things being measured, we don't want to > store the data in columns. Rather we are likely going to define 3 tables. We > have not created them yet, but they would be something like this: > > ThingsWeMeasure, Patients, PatientsXThingsWeMeasure. > > PatientsXThingsWeMeasure would look something like this: > PatientID int NOT NULL > MeasurementID int NOT NULL > WhenWeMeasuredIT datetime NOT NULL > Value varchar(10) NOT NULL > > So my specific question is do we keep WhenWeMeasuredIT as it is? Or should > we split the date and time columns out, with a "TimeWeMeasuredIt" column > that is nullable? > > Thoughts, opinions, perspective? > > Thanks! > > > Jeremy wrote:
> I posted a question earlier today (9:25 AM). Subject: Storing DateTime How are date and time being captured? Are your users filling in a > Values - Sometimes with time, Sometimes without. > > So my specific question is do we keep WhenWeMeasuredIT as it is? Or should > we split the date and time columns out, with a "TimeWeMeasuredIt" column > that is nullable? > > Thoughts, opinions, perspective? > > Thanks! > > "Date" field and a "Time" field in an application, or are you just datestamping the records as they are created? If the users are inputting seperate values, I would: a. Add a bit field TimeSpecified - 1 indicates a time was recorded, 0 means no time recorded b. Store the date/time combo as a single datetime field. A value of '7/13/2006 00:00:00' with a TimeSpecified value of 0 means the reading was taken "sometime" on 7/13/2006. A value of '7/13/2006 00:00:00' with a TimeSpecified value of 1 means the reading was taken at midnight on 7/13/2006. Hope that helps... RE:
<< Hope that helps... >> Yes - your response and all others (so far) have been very helpful. All good ideas from which me and my team can consider as we make our design decisions. Thanks for *not* bringing up Zeno's paradox... Show quote :-) "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:enMDdU0pGHA.1600@TK2MSFTNGP04.phx.gbl... > Jeremy wrote: >> I posted a question earlier today (9:25 AM). Subject: Storing DateTime >> Values - Sometimes with time, Sometimes without. >> >> So my specific question is do we keep WhenWeMeasuredIT as it is? Or >> should we split the date and time columns out, with a "TimeWeMeasuredIt" >> column that is nullable? >> >> Thoughts, opinions, perspective? >> >> Thanks! > > How are date and time being captured? Are your users filling in a "Date" > field and a "Time" field in an application, or are you just datestamping > the records as they are created? > > If the users are inputting seperate values, I would: > > a. Add a bit field TimeSpecified - 1 indicates a time was recorded, 0 > means no time recorded > b. Store the date/time combo as a single datetime field. A value of > '7/13/2006 00:00:00' with a TimeSpecified value of 0 means the reading was > taken "sometime" on 7/13/2006. A value of '7/13/2006 00:00:00' with a > TimeSpecified value of 1 means the reading was taken at midnight on > 7/13/2006. > > Hope that helps... > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com  
Other interesting topics
|
|||||||||||||||||||||||
