ED2 KC- Date/time field value out of range

---

Example:

ERROR: date/time field value out of range: "0000-12-01"; nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-12-01"

This error is related to: ERROR: cannot drop table because other objects depend on it

select uid as event_UID, executiondate as event_Date from programstageinstance where executiondate > 'YYYY-MM-DD' order by executiondate

Resolution #1: To resolve this issue, identify the outlying data and remove it using the SQL query above. Then re-run analytics. If that doesn’t work see below:

Alternatively this can be solved by finding the offending data by via the API – at least the program should be found in the logs.

Take for example this error:

ERROR: date/time field value out of range: "0000-12-01"; nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-12-01"

It is not found using the query above because it is not an event – it is likely a tracked entity attribute in an enrollment. What we need to do is find the SQL Query in the logs that is associated with that error - it gives us a very important clue - the program’s serial ID which we can check using SQL to get the program name. That narrows our search for this bad date immensely.

……psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc('month', psi.executiondate) as date)=ougs.startdate or ougs.startdate is null) inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(psi.executiondate as date)=dps.dateperiod where psi.lastupdated < '2022-05-19T14:57:24' and psi.executiondate >= '2022-01-01T00:00:00' and psi.executiondate < '2023-01-01T00:00:00' and pr.programid=18503 and psi.organisationunitid is not null and psi.executiondate is not null and psi.deleted is false ]; ERROR: date/time field value out of range: "0000-12-01"; nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-12-01"

In this case the programid=18503. Now I can use the SQL Viewer app or sql views or a DB client to search for that program id’s name.

Once I find the program name I can use the API to search all 3 types of tracker data - TEIs, enrollments, and events for the bad date.

TEIs

.../api/trackedEntityInstances.json?program=<UID>&ou=<ROOT OU UID>&ouMode=DESCENDANTS&paging=false

Enrollments

.../api/enrollments.json?program=<UID>&ou=<ROOT OU UID>&ouMode=DESCENDANTS&paging=false

Enrollments are a bit more structured so you could filter for Enrollment date or other date fields using this parameter: &filter=enrollmentDate:eq:<INSERT BAD DATE>

Events

Events are a little easier in SQL which is why the first solution is listed.

.../api/events.json?program=NKxEzSvTGVB&ou=Q26rFT1bNnN&ouMode=DESCENDANTS&paging=false

Here is some more advanced SQL to check JSONB data values in program stage instances:

SELECT psi.uid           AS event_uid,
       psi.executiondate as exec_date,
       de.name           AS dataelement_name,
       key               AS dataelement_uid,
       value ->> 'value' AS valueFROM programstageinstance psi, jsonb_each(eventdatavalues)
JOIN dataelement de ON de.uid = keyWHERE de.uid IN ('MsslEPuKLvc','SsBWMe5Shhr','LamkA1QMZWN','UHNjYenLB4T','M4DZgD0OXCJ','epmIBD8gh7G','z2iIYpHMWQE','B5m9uUPna2R','s4VyDhKoQjU','fdaIDbNwRGK','yTSlwP6htQh','TJPxuJHRA3P','r0zBP8h3UEl','nHicA9GX9dM','qitG6coAg3q','Ti0huZXbAM0','a01lnSNuGH6','ESTGBpvuFur','ctehL7PePvu','Q5W46DuXOh7','CWqTgshbDbW','sDMDb4InL5F','x23vKt36h56','RECl06RNilT','Mn2bloTIniL','BPYJY12LHMJ','Gc1JB7MCHaN','mNE4lX1em1s','plbCng5P1uh','J57UWRdszUE','NXi3ARttBR8','b7WCStMuKGV','aFn0bEgPu5t','yGiFYHfsSC8','ecweHE9hspo','aMVG1Z6yOsj','ZFpNv2lohYT','rB2n2XTgAuG');

And to search all data values (which is sometimes the only way to to find things with this error):

SELECT value ->> 'value' FROM programstageinstance psi, jsonb_each(eventdatavalues) order by value ASC;