Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
Common Data Model (CDM)
Convert Database to CDM (ETL)
Tool Specific Documentation
This is an old revision of the document!
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.
Here is the current use of date and time fields in CDM v5.01:
PERSON
SPECIMEN
DEATH
VISIT_OCCURRENCE
PROCEDURE_OCCURRENCE
DRUG_EXPOSURE
DEVICE_EXPOSURE
CONDITION_OCCURRENCE
MEASUREMENT
NOTE
OBSERVATION
And the ERA tables, COHORT tables, and PAYER_PLAN_PERIOD table.
We propose adding a time field or fields to the following tables
A time field would be added to each date field. Date fields would not be changed.
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 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 the time field as timestamp, which allows fast operations but produces redundancy.
Are the time 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.
Should a 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.
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.