I am writing to request your assistance with a critical issue we are encountering in our DHIS2 2.40.3 system.
Our system is a tracker system with the following programs: NACP Combined ART Register, NLTCP TB Patient Tracker, and Skin NTDs Register. Until recently, it was functioning normally. However, we have started to experience errors in the analytics table update, specifically the following message:
“Oct 14 06:08:39 hmis2 tomcat9[326]: Caused by: org.postgresql.util.PSQLException: ERROR: date/time field value out of range: “0000-02-09”;”
We have conducted a thorough search within our system but have been unable to locate any data with the invalid date “0000-02-09.”
Attached are the Catalina log, program stage query output, and analytics table screenshots that may help troubleshoot this issue.
We would greatly appreciate your expertise in resolving this matter promptly
You should check the tracked entity attributes or data elements values.
For you 0000-02-09 is perhaps a string with 209 in your db ?
you can perhaps try this
create a function to detect if a date is valid or not
CREATE OR REPLACE FUNCTION is_valid_date(p_date TEXT)
RETURNS BOOLEAN AS $$
DECLARE
parsed_date DATE;
BEGIN
-- Try to cast the string to a date implicitly
parsed_date := p_date::DATE;
RETURN TRUE;
EXCEPTION
-- If an exception occurs (invalid date), return FALSE
WHEN others THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
then search on the trackedentityattributevalue ?
select * from trackedentityattributevalue
join trackedentityattribute ON trackedentityattribute.trackedentityattributeid = trackedentityattributevalue.trackedentityattributeid
where trackedentityattribute.valuetype in( 'DATE', 'AGE') and
is_valid_date(trackedentityattributevalue.value) = false
I think this happened to me, it was a bug in the mobile application, sending incorrect dates. They are stored as string in a json field and so they are not revalidated by the backend (to verify they are plausible date).
Dear Stephan,
I did as suggested but when I ran the query: hmis2=# \COPY (SELECT trackedentityattributevalue., trackedentityattribute. FROM trackedentityattributevalue JOIN trackedentityattribute ON trackedentityattribute.trackedentityattributeid = trackedentityattributevalue.trackedentityattributeid WHERE trackedentityattribute.valuetype = ‘DATE’ AND NOT is_valid_date(trackedentityattributevalue.value)) TO ‘attribute.csv’ WITH CSV HEADER; COPY 0
The output is 0 row
can you try to run this query in order to see if that value is a TEA value?
SELECT * FROM trackedentityattributevalue WHERE value ='0000-02-09';
Also, as @Stephan_Mestach mentioned, this could be related with an issue of old version of Android app (that it is solved and reflected in the jira ticket).
But it could be also related with custom apps using the REST API, because there is a linked issue that it is not solved yet [DHIS2-17679] - Jira
Dear Carlos,
I am writing to provide an update on how i resolved the invalid date issue we were experiencing in our DHIS2 system.
I was able to successfully address the problem by implementing the following steps:
1. Updated Trigger Function:
CREATE OR REPLACE FUNCTION update_trackedentityattributevalue()
RETURNS TRIGGER AS $$
BEGIN
NEW.lastupdated := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
2. Disabled Previous Trigger:
ALTER TABLE trackedentityattributevalue DISABLE TRIGGER update_trackedentityattributevalue_trigger;
3. Updated Invalid Date:
UPDATE trackedentityattributevalue SET value = ‘2024-02-09’ WHERE value = ‘0000-02-09’;
4. Re-enabled Trigger:
ALTER TABLE trackedentityattributevalue ENABLE TRIGGER update_trackedentityattributevalue_trigger;
I would like to thank @Stephan_Mestach and @ctejo for their valuable contributions and guidance in resolving this issue. Their expertise and insights were instrumental in identifying and correcting the errors in my previous approach.
@Stephan_Mestach As the valuetype “AGE” is also affected for this issue, maybe this part of the query should be updated in order to search also AGE valuetype
