Date Normalization - general discussion

[niquola]

[gotdan]

We should consider transforming all date and datetime elements into ranges to make partial dates queryable. For example, in many use cases, a date of 2022 implies a date range of 2022-01-01 to 2022-12-31 for the purpose of comparing to other dates, and this is a slow conversion to do on every query.

[Aitem]

This may not be relevant to the topic of the discussion, but I will give my thoughts on working with dates in FHIR from an analytical point of view

In the context of working with dates, I would distinguish three areas

• checking the validity of the data
• date casts
• extraction

Validation

It is a fact that in practice we often work with data that was downloaded from various sources and can contain errors. Sometimes it is critical for the next step of cast to date

Example: Finding invalid Observation.issued

select id
from "Observation"
where resource#>>'{ issued }' NOT SIMILAR TO '([0-9]([0-9]([0-9][1-9]|[1-9]0)|[1-9]00)|[1-9]000)(-(0[1-9]|1[0-2])(-(0[1-9]|[1-2][0-9]|3[0-1])(T([01][0-9]|2[0-3]):[0-5][0-9]:([0-5][0-9]|60)(\.[0-9]{1,9})?)?)?(Z|(\+|-)((0[0-9]|1[0-3]):[0-5][0-9]|14:00)?)?)?' );

Cast to date

It would be great to think about casts FHIR string and complex dates to native date types

FHIR date types

• date
• dateTime
• instant
• time
• period

Example: Patient

select   id
       , ("resource"#>>'{ birthDate }')::date birthdate
       , ("resource"#>>'{ deceased,dateTime }')::date deceased 
  from Patient

Extraction

In analytics tasks, we rarely operate with raw times with millisecond precision, instead, we often work with years, months, quarters, and weeks.
Providing a set of usable functions/helpers to operate with the dates would be great.

something like this

• age - calculate full years between a given date and NOW
• duration - duration of period date
• extract year, month .. from given date for the aggregation

Example: Patient flat view

  SELECT id
         , DATE_PART('year', AGE(date("resource" #>>'{birthDate}'))) age
         , ("resource"#>>'{ birthDate }')::date birthdate
         , extract('YEAR' from ("resource"#>>'{ birthDate }')::date) birth_year
         , ("resource"#>>'{ deceased,dateTime }')::date deceased 
         , extract('YEAR' from ("resource"#>>'{ deceased,dateTime }')::date) deceased_year
         , extract('year' FROM age(("resource"#>>'{ deceased,dateTime }')::date
                                    , ("resource"#>>'{ birthDate }')::date)) death_age
    FROM Patient

Example: Encounter flat view duration

SELECT id
       , ("resource"#>>'{ period,start }')::date start
       , ("resource"#>>'{ period,end }')::date end
       , ("resource"#>>'{ period,end }')::timestamp - ("resource"#>>'{ period,start }')::timestamp duration
  FROM Encounter