Differences

This shows you the differences between two versions of the page.

--- documentation:next_cdm:time [2016/10/04 17:53] – vojtechhuser
+++ documentation:next_cdm:time [2017/07/06 15:05] (current) – clairblacketer
@@ Line 1: / Line 1: @@
 ===== Add Time Field to various Occurrence and Exposure Tables =====
+=== Proposals are now being stored as github issues ===
+[[https://github.com/OHDSI/CommonDataModel/issues/60|link to issue in github]]
-While v5 visit_occurrence table supports both date and time of event, other occurrence tables and exposure tables such as drug, condition, and procedure support only the date level.  It is desirable to have the option to include specific time data for such occurrences.
+While v5 visit_occurrence table supports both date and time of event, other occurrence tables and exposure tables such as drug, condition, and procedure support only the date level.  It is desirable to have the option to include specific time data for such occurrences. Our goal is to allow temporal operations finer than day without disrupting OHDSI by requiring major recoding.
-We propose adding time field to the following tables
+Here is the current use of date and time fields in CDM v5.01:
+PERSON
+  * year_of_birth as integer, required
+  * month_of_birth as integer, not required
+  * day_of_birth as integer, not required
+  * time_of_birth as time, not required
+SPECIMEN
+  * specimen_date as date, required
+  * specimen_time as time, not required
+DEATH
+  * death_date as date, required
+VISIT_OCCURRENCE
+  * visit_start_date as date, required
+  * visit_start_time as time, not required
+  * visit_end_date as date, required
+  * visit_end_time as time, not required
+PROCEDURE_OCCURRENCE
+  * procedure_date as date, required
+DRUG_EXPOSURE
+  * drug_exposure_start_date as date, required
+  * drug_exposure _end_date as date, not required
+DEVICE_EXPOSURE
+  * device_exposure_start_date as date, required
+  * device _exposure _end_date as date, not required
+CONDITION_OCCURRENCE
+  * condition_start_date as date, required
+  * condition _end_date as date, not required
+MEASUREMENT
+  * measurement_date as date, required
+  * measurement _time as time, not required
+NOTE
+  * note_date as date, required
+  * note_time as time, not required
+OBSERVATION
+  * observation_date as date, required
+  * observation _time as time, not required
+And the ERA tables, COHORT tables, and PAYER_PLAN_PERIOD table.
+=== DECISION 1 ===
+We have a choice between adding fields of type time, which require significant processing to determine durations (join the date and time, and then operate on that) versus defining a datetime field as timestamp, which allows fast operations but produces redundancy.
+[Suggest datetime = timestamp.]
+=== DECISION 2 ===
+We propose adding a datetime field or fields to the following tables
   * Condition_Occurrence
   * Procedure_Occurrence
@@ Line 10: / Line 71: @@
   * Death
-This would support handling data from ICU, Emergency Department, infusions, post-procedure care, etc where multiple events occur on the same day and sequence matters.  This granularity would also support the incorporation of data generated from tracking devices.
+We propose replacing the current time fields with datetime fields in the following tables
+  * Specimen
-=== Goal ===
+  * Visit_Occurrence
-Allow temporal operations finer than day without requiring recoding the OHDSI software, and consider allowing durations ===
+  * Measurement
+  * Note
-=== PROPOSAL 1 ===
+  * Observation
-. Throughout the CDM tables, keep all *_DATE fields as is, still in DATE format. These are generally required, non-null fields.
-. For each *_DATE field, add a *_TIME field of type DATETIME = TIMESTAMP, and make those fields optional.
-(for reference, link to existing spec (for example [[documentation:cdm:procedure_occurrence|Procedures]])
+A datetime field (also known as timestamp field) would be added to each date field. Date fields would not be changed.
-=== PROPOSAL 1a ===
+This would support handling data from ICU, Emergency Department, infusions, post-procedure care, etc. where multiple events occur on the same day and sequence matters. This granularity would also support the incorporation of data generated from tracking devices.
-As above 1-2 and
+In addition, current *_TIME fields would be removed. At this point, all current software will continue working with the *_DATE fields, and over time we will develop extensions to the software to accommodate DATETIME in different database management systems.
-. Add a *_TIME_GRAN granularity field for each new DATETIME field, which would indicate year, month, day, hour, minute, or second as the timestamp's granularity. When a timestamp is filled in, it should be set to the first valid time in that indicated interval. E.g., 1990-12-01-00:00:00.000000 is the correct entry for December, 1990.
+[Suggest moving forward with fields called *_DATETIME, removing the *_TIME fields. The *_DATE fields will remain required.]
+=== DECISION 3 ===
-=== PROPOSAL 1b ===
+Are the datetime fields required. Making them required allows developers to begin to use them with a potential migration from date+time or date+timestamp to timesteamp in the future, but it forces CDM builders to enter unknown times or timestamps. If required, the default time will be the first instant in the allowable period. E.g.,  1990-12-01-00:00:00.000000 is the correct entry for December, 1990.
-As above 1-2, optionally 3, and
+[Suggest optional for now.]
-. Add a BIRTH_TIME timestamp column to the PERSON table to speed age calculations.
+=== DECISION 4 ===
+Should a date time (timestamp) field be added to BIRTH (or alternatively should PERSON.time_of_birth be changed to timestamp). This will allow more rapid calculation of age but will be redundant with the current information.
-=== PROPOSAL 2 ===
+[Suggest add BIRTH_DATETIME. Remove TIME_OF_BIRTH.]
-Independent of 1-4, it was suggested that all tables have *_START_DATE and *_END_DATE fields for all domains. This means PROCEDURE_OCCURRENCE, MEASUREMENT, OBSERVATION will be changing from having only 1 date to having 2 dates, and both would required (even if in some systems END_DATE = START_DATE).
+=== DECISION 5 ===
+Should we add a *_TIME_GRAN granularity field for each new timestamp field, which would indicate year, month, day, hour, minute, or second as the timestamp's granularity. When a timestamp is filled in, it should be set to the first valid time in that indicated interval.
+[Suggest defer decision.]