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 as00: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

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)

OHDSI / Themis