How to fix broken dates
In some cases, you can have invalid data in your database- after importing data, or an error in SQL migrations.
For example, we have Patient resource with incorrect birthDate in the database. If we will try to search Patient by date, we will catch an error:
get
/fhir
/Patient?birthDate=2020-02-01
To fix this error we should find all incorrect dates in table via SQL query
1
select max((resource#>>'{birthDate}')::date) from patient;
Copied!
In response we catch an error
1
ERROR: date/time field value out of range: "2020-02-30"
Copied!
Now we can fix these dates:
1
update patient
2
set resource = resource || '{"birthDate": "2020-02-29"}'
3
where resource#>>'{birthDate}' = '2020-02-30';
Copied!
Copy link
Contents
get