what to do with DATETIME columns when the raw data has no time or is given as 00:00:00

[solmazeradat]

what to do with DATETIME columns when the raw data has no time or is given as 00:00:00

CDM or THEMIS convention?

THEMIS

Table or Field level?

Field

Is this a general convention?

Applies to multiple fields

Summary of issues

• How to populate the DATETIME columns for fields in various CDM tables such as:

observation_period.observation_period_start_datetime
observation_period.observation_period_end_datetime
procedure_occurrence.procedure_datetime
drug_exposure.drug_exposure_start_datetime
device_exposure.device_exposure_start_datetime
condition_occurrence.condition_start_datetime

if time is unknown or is given as 00:00:00

Summary of answer

Final response on the post provides the following recommendation:

One of the items that came out of the OHDSI Symposium Themis F2F was what to do with DATETIME columns when the raw data has no time.

• What do you do for start times when all you have is day data?
• What do you do for end times when all you have is day data?

Here is what was discussed but we would like to open it for comment.

RECOMMENDATION
Choose default time of 00:00:00 if time is unknown. If you do have time and receive a time of 00:00:00 just shift 1 second to 00:00:01

ACTION
Work with @clairblacketer to have posted on the Data Model Conventions page under the CDM Wiki under convensions. https://github.com/OHDSI/CommonDataModel/wiki/Data-Model-Conventions

Related links

• Forum post link - https://forums.ohdsi.org/t/should--datetime-fields-be-required/3686
• Last response on the forum post - https://forums.ohdsi.org/t/should--datetime-fields-be-required/3686/12?u=solmaz.eradat

Other comments/notes

• Please use this section to add any other relevant notes or comments. (i.e. if links are not working, something is out of date or deprecated)

[MelaniePhilofsky]

The datetime field is nullable, only date fields are required fields in the CDM. This is an old post in reference to when CDM v6.0 was introduced. The OHDSI community no longer supports this CDM because of the requirement to fill datetime fields. For more information, see this forum post https://forums.ohdsi.org/t/omop-version-6-put-on-ice/18823