Observational Health Data Sciences and Informatics

This is an old revision of the document!

---

GOAL: Allow temporal operations finer than day without requiring recoding the OHDSI software, and consider allowing durations.

PROPOSAL1:

1. Throughout the CDM tables, keep all *_DATE fields as is, still in DATE format. These are generally required, non-null fields.

2. For each *_DATE field, add a *_TIME field of type DATETIME = TIMESTAMP, and make those fields optional.

MODIFIED PROPOSAL1a:

As above 1-2 and

3. 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.

MODIFIED PROPOSAL1b:

As above 1-2, optionally 3, and

4. Add a BIRTH_TIME timestamp column to the PERSON table to speed age calculations.

PROPOSAL2:

5. 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).