|
|
You're in the
sql server programming
group:DateTime Question (Again)
sql server programming:
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!
[quoted text, click to view]
Jeremy wrote: > 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. I didn't read the original thread, but.... 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
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? -- Arnie Rowland* "To be successful, your heart must accompany your knowledge." [quoted text, click to view] "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! >
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 [quoted text, click to view] "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) -- Wayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. [quoted text, click to view] "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! > >
[quoted text, click to view]
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
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... :-) [quoted text, click to view] "Tracy McKibben" <tracy@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
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |