Data Export Issue in DHIS2 Analytics [FIXED]

Support - Assistance technique
1

Dear DHIS2 Community,
I am writing to seek assistance regarding an issue I am facing while exporting data to Analytics in DHIS2. I am encountering a problem where the process gets stuck, and I receive the following error message:

"PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 38,043 insert into _dateperiodstructure_temp values ('0100-02-29 00:00:00+00',100,'1000227','100W8','100WedW9','100ThuW9','100SatW9','100SunW8','100BiW4','10002','10001B','100Q1','100NovQ2','100S1','99AprilS2','100NovS1','100','99April','99July','99Oct','100Nov') was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 38,043 insert into _dateperiodstructure_temp values ('0100-02-29 00:00:00+00',100,'1000227','100W8','100WedW9','100ThuW9','100SatW9','100SunW8','100BiW4','10002','10001B','100Q1','100NovQ2','100S1','99AprilS2','100NovS1','100','99April','99July','99Oct','100Nov') was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch."

I am seeking your expertise and guidance to overcome this issue. It appears that there is an error related to a date/time field value being out of range (“0100-02-29 00:00:00+00”). Could you please assist me in resolving this problem? I would greatly appreciate any insights or suggestions you can provide.

Thank you very much for your attention and support. I look forward to hearing from you soon.

2

Hi @elmoujarrade

Why are there periods with the year “0100”? It’s not clear how this would happen. Were you importing data from a spreadsheet?

It wasn’t clear at first but one speculation could be that the dates got mixed up between time zone and year i.e. GMT+01:00 (“0100”) is the time zone in Morocco. This might happen maybe if the sheet was RTL and somehow the dates were inserted backward?

The issue is from the data values inserted into the database. Are there any clues you can see when running the analytics tables export that might lead to where these dates are? The way to solve this is understand where these incorrect values are from.

I hope this helps. Thanks!

1 Like
3

Hi @Gassim
I checked the logs by the chrome dev tool, and noticed that the process stops at [9/11] when generating the DatePeriod table. The error displayed is:

[{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.638”,“message”:“Process failed: java.util.concurrent.CancellationException”,“completed”:true,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.397”,“message”:“Process failed: java.util.concurrent.CancellationException”,“completed”:true,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.315”,“message”:“processing aborted: cancelled as failing item caused request for cancellation”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“ERROR”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T16:12:54.313”,“message”:“PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 38,043 insert into _dateperiodstructure_temp values (‘0100-02-29 00:00:00+00’,100,‘1000227’,‘100W8’,‘100WedW9’,‘100ThuW9’,‘100SatW9’,‘100SunW8’,‘100BiW4’,‘10002’,‘10001B’,‘100Q1’,‘100NovQ2’,‘100S1’,‘99AprilS2’,‘100NovS1’,‘100’,‘99April’,‘99July’,‘99Oct’,‘100Nov’) was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 38,043 insert into _dateperiodstructure_temp values (‘0100-02-29 00:00:00+00’,100,‘1000227’,‘100W8’,‘100WedW9’,‘100ThuW9’,‘100SatW9’,‘100SunW8’,‘100BiW4’,‘10002’,‘10001B’,‘100Q1’,‘100NovQ2’,‘100S1’,‘99AprilS2’,‘100NovS1’,‘100’,‘99April’,‘99July’,‘99Oct’,‘100Nov’) was aborted: ERROR: date/time field value out of range: "0100-02-29 00:00:00+00" Call getNextException to see other errors in the batch.”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.351”,“message”:“Generating resource tables”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.347”,“message”:“Drop SQL views”,“completed”:false,“id”:“THwaXM3P8tb”},{“uid”:“THwaXM3P8tb”,“level”:“INFO”,“category”:“ANALYTICS_TABLE”,“time”:“2023-06-30T15:05:06.343”,“message”:“Analytics table update process started”,“completed”:false,“dataType”:“PARAMETERS”,“data”:{“lastYears”:null,“skipTableTypes”:[],“skipPrograms”:[],“skipResourceTables”:false},“id”:“THwaXM3P8tb”}]

I would appreciate any guidance or insights you can provide to help me identify the source of the incorrect dates and resolve the error. Thank you for your support.

Best regards,

4

Hi @elmoujarrade,
It seems there are data values for some incorrect dates in your database. You will need to fish out the datavalues/events and delete them. Is your implementation tracker or aggregate?

1 Like
5

Hello, i open it as jira issue too, but maybe somebody have some workaround to solve this problems.

https://dhis2.atlassian.net/browse/DHIS2-15573

Since at least the last two build we are experiment errors with the analytics.
in the continuous analitycs we can see in the logs:
Caused by: org.postgresql.util.PSQLException: ERROR: column dps.quarterlynov does not exist
Hint: Perhaps you meant to reference the column “dps.quarterly”.

And when we execute the full analytics after remove anlaytics tables:
core_1 | Caused by: org.postgresql.util.PSQLException: ERROR: date/time field value out of range: “1100-02-29 01:14:44+01”

In other instance with diferent data:
2023-07-03 11:33:30 PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; Batch entry 1,519 insert into
_dateperiodstructure_temp values (‘0200-02-29 01:14:44+01’,200,‘2000228’,‘200W9’,‘200WedW9’,‘200ThuW9’,‘200SatW8’,‘200SunW9’,‘200BiW5’,‘20002’,‘20001B’,‘200Q1’,‘200NovQ2’,‘200S1’,‘199AprilS2’,‘200NovS1’,‘200’,‘199April’,‘199July’,‘199Oct’,‘200Nov’) was aborted: ERROR: date/time field value out of range: “0200-02-29 01:14:44+01” Where: unnamed portal parameter $1 = ‘…’ Call getNextException to see other errors in the batch.; nested exception is java.sql.BatchUpdateException: Batch entry 1,519 insert into _dateperiodstructure_temp values (‘0200-02-29 01:14:44+01’,200,‘2000228’,‘200W9’,‘200WedW9’,‘200ThuW9’,‘200SatW8’,‘200SunW9’,‘200BiW5’,‘20002’,‘20001B’,‘200Q1’,‘200NovQ2’,‘200S1’,‘199AprilS2’,‘200NovS1’,‘200’,‘199April’,‘199July’,‘199Oct’,‘200Nov’) was aborted: ERROR: date/time field value out of range: “0200-02-29 01:14:44+01” Where: unnamed portal parameter $1 = ‘…’ Call getNextException to see other errors in the batch.

(This date is invalid due 02-29 not exist in the year 200, but i dont know where come from that date)

2 Likes
6

Hello @idelcano

I have noticed you are facing the same exact issue as me. I took time to study the problem and we discovered that the resource tables is the one causing this issue. I assume you are also failing to complete resource table generation. Whats happening happening that _dateperiodstructure table doesnt have the column QuarterlyNov thus failing to proceed with analytics. If you check carefuly, there should be a table _dateperiodstructure_temp. Alter this table to _dateperiodstructure and alter the other _dateperiodstructure to _dateperiodstructure_temp and run the analytics by checking skip resource table. That is how I bypassed the error and can run analytics but failing resource table. This appears to be a bug big time. Have a look at my post, I have seen your Jira, I will comment and vote.

https://community.dhis2.org/t/looping-resource-table-when-generating-dateperiodstructure/53934

Regards
Moses

2 Likes
7

Dear Team,
May I have your assistance on this matter, patch release 2.38.4.3 has brought trouble on my instance, Resource table generation cant past run _dateperiodstructure. Catalina.out is silent ans well as database logs. The only thing I can see under network is a loop

loop
loop1890×952 89.1 KB

My catalina.out log is stack like mentioned

[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,170 Resource table '_categoryoptioncomboname' update done: '00:00:04.206' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,283 Generating resource table: '_dataelementgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:36] [info] * INFO  2023-07-11T20:30:36,975 Resource table '_dataelementgroupsetstructure' update done: '00:00:00.691' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,006 Generating resource table: '_indicatorgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,030 Resource table '_indicatorgroupsetstructure' update done: '00:00:00.023' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:37] [info] * INFO  2023-07-11T20:30:37,064 Generating resource table: '_organisationunitgroupsetstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:38] [info] * INFO  2023-07-11T20:30:38,648 Resource table '_organisationunitgroupsetstructure' update done: '00:00:01.583' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:38] [info] * INFO  2023-07-11T20:30:38,694 Generating resource table: '_categorystructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:41] [info] * INFO  2023-07-11T20:30:41,707 Resource table '_categorystructure' update done: '00:00:03.013' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:41] [info] * INFO  2023-07-11T20:30:41,889 Generating resource table: '_dataelementstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * INFO  2023-07-11T20:30:44,187 Resource table '_dataelementstructure' update done: '00:00:02.296' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * INFO  2023-07-11T20:30:44,281 Generating resource table: '_periodstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:44] [info] * WARN  2023-07-11T20:30:44,542 Duplicate ISO date for period, ignoring: [DEFAULT | Yearly: 1995-01-01 - 1995-12-31], ISO date: 1995 (PeriodResourceTable.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:45] [info] * INFO  2023-07-11T20:30:45,385 Resource table '_periodstructure' update done: '00:00:01.103' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs
[2023-07-11 20:30:45] [info] * INFO  2023-07-11T20:30:45,802 Generating resource table: '_dateperiodstructure' (JdbcResourceTableStore.java [taskScheduler-19]) UID:pezfJF9XZJs

We can stay on this table ‘_dateperiodstructure.’ for ages.

Anyone familiar with this cause?

1 Like
8

Hi @elmoujarrade

It seems two other community members (@idelcano and @moses_mwale) are facing the same issue:

I have a feeling that the out of range date such as 0100 and 0200 is because the timezone is being mixed up somewhere. I’m going to ask for support so that someone from the team can investigate this further. Thanks!

9

As noted by @medallion , it looks like this date (February 29th 100) is the problem. Note the year…100 AD, some 1900+ years in the past, would appear to be invalid. This likely results from some data entry problem with a user mistyping 100 for 2020 for instance. Look in the period table to see if you can find the periodid for this extremely old period. Once you find it, you will need to correct/remove the data which is associated with it in the datavalue table. Once you find all data which is associated with this period and have moved it to the correct period (or removed it), restart your Tomcat server and try to run analytics again. If it doesn’t work, let us know!

2 Likes
10

@jason, is this a new validation that came with the recent releases? We have always had data with these erroneous dates in the system, but the resource table has always been working respecting only the number of years set in the “last years” to include. Our instance currently has close to 1.5k TEIs being followed up across 10+ tracker programs since 2016, It will not be feasible to identify all the data falling out of the current range offset, and increasing this value to include the bad data would mean an entire day or 2 days of updating Analytics tables (currently at about 7 hours). Is it possible to have a more feasible solution?

Range of years allowed (based on your system settings and existing data): [2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024].
 Years out of range found: [197, 198, 199, 200, 201, 202, 1990, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2029, 2091, 2107, 2109, 2110, 2111, 2112, 2113, 2114].
	at org.hisp.dhis.resourcetable.DefaultResourceTableService.checkYearsOffset(DefaultResourceTableService.java:228) ~[dhis-service-administration-2.39.3.jar:?] at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:187) ~[dhis-service-administration-2.39.3.jar:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
1 Like
11

Tagging @maikel who may be able to advise better.

2 Likes
12

Hi @here

@maikel has mentioned the steps for a workaround to solve the issue in the Jira bug issue, and the good news as Maikel said is that it will be fixed in the next patch release so if you wait and update the issue will stop.

Thank you!

1 Like
13

Yes, we managed to resolve the issue temporarily using @maikel’s guide. Thank you once more.

1 Like
14

Just to confirm, if you are using 2.38.4, the update to 2.38.5 is ready: DHIS2 patch release 2.38.5 is now available

Please update to fix the issue. Thanks! :slightly_smiling_face::partying_face:

1 Like
15

Dear Team
while generating resource table in version 2.39.3 we got this error due to this reason one of the resource tabe not created – _dateperiodstructure

resource table generation issue

Your database contains years out of the allowed offset. Range of years allowed (based on your system settings and existing data): [2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028]. Years out of range found: [1988, 1989, 1990, 1991, 1992, 1993, 2000].

please suggest whare the store and in which table contain database contains years out of the allowed offset. its shows based on your system settings and existing data)

Thanks and Regards

Mithilesh Kumar Thakur

16

Hi @Mithilesh_Kumar_Thak

Welcome back to the community! This is a well known bug which as been fixed ([DHIS2-15573] - Jira). For version 2.39.3, you will need to update to 2.39.3.1 which is already released or 2.39.4 once it is released.

Thank you! I’m merging your post with the topic discussion about this issue.

17

@Gassim thanku very much its working

18

@Gassim I am currently experiencing this problem nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-11-30"

range
range1276×988 200 KB

I did all the data cleaning for all the dates as discussed in the JIRA.

This is version 2.39.4, Build revision 94310fb

Below is a full error log

 [
INSERT INTO analytics_enrollment_temp_kieuz3dtepm
            (
                        "uidlevel1",
                        "uidlevel2",
                        "uidlevel3",
                        "uidlevel4",
                        "uidlevel5",
                        "uidlevel6",
                        "uidlevel7",
                        "daily",
                        "weekly",
                        "weeklywednesday",
                        "weeklythursday",
                        "weeklysaturday",
                        "weeklysunday",
                        "biweekly",
                        "monthly",
                        "bimonthly",
                        "quarterly",
                        "quarterlynov",
                        "sixmonthly",
                        "sixmonthlyapril",
                        "sixmonthlynov",
                        "yearly",
                        "financialapril",
                        "financialjuly",
                        "financialoct",
                        "financialnov",
                        "XlWhhCvkNhv",
                        "plbdIxS05ws",
                        "g3QoANebtvt",
                        "NUuUjfCQqj3",
                        "P9lfonpL2dg",
                        "X6HKRWOowJw",
                        "JfKaih9Kvrb",
                        "CqooW7qp7Lh",
                        "pi",
                        "enrollmentdate",
                        "incidentdate",
                        "completeddate",
                        "lastupdated",
                        "storedby",
                        "createdbyusername",
                        "createdbyname",
                        "createdbylastname",
                        "createdbydisplayname",
                        "lastupdatedbyusername",
                        "lastupdatedbyname",
                        "lastupdatedbylastname",
                        "lastupdatedbydisplayname",
                        "enrollmentstatus",
                        "longitude",
                        "latitude",
                        "ou",
                        "ouname",
                        "oucode",
                        "oulevel",
                        "pigeometry",
                        "registrationou",
                        "tei",
                        "teigeometry"
            )
SELECT     ous."uidlevel1",
           ous."uidlevel2",
           ous."uidlevel3",
           ous."uidlevel4",
           ous."uidlevel5",
           ous."uidlevel6",
           ous."uidlevel7",
           dps."daily",
           dps."weekly",
           dps."weeklywednesday",
           dps."weeklythursday",
           dps."weeklysaturday",
           dps."weeklysunday",
           dps."biweekly",
           dps."monthly",
           dps."bimonthly",
           dps."quarterly",
           dps."quarterlynov",
           dps."sixmonthly",
           dps."sixmonthlyapril",
           dps."sixmonthlynov",
           dps."yearly",
           dps."financialapril",
           dps."financialjuly",
           dps."financialoct",
           dps."financialnov",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36597) AS "XlWhhCvkNhv",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36599) AS "plbdIxS05ws",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36592) AS "g3QoANebtvt",
           (
                  SELECT cast(value AS timestamp)
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36595
                  AND    value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$') AS "NUuUjfCQqj3",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36593) AS "P9lfonpL2dg",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36598) AS "X6HKRWOowJw",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36594) AS "JfKaih9Kvrb",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36596) AS "CqooW7qp7Lh",
           pi.UID,
           pi.enrollmentdate,
           pi.incidentdate,
           CASE pi.status
                      WHEN 'COMPLETED' THEN pi.enddate
           END,
           pi.lastupdated,
           pi.storedby,
           pi.createdbyuserinfo ->> 'username'  AS createdbyusername,
           pi.createdbyuserinfo ->> 'firstName' AS createdbyname,
           pi.createdbyuserinfo ->> 'surname'   AS createdbylastname,
           CASE
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN NULL
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN trim(pi.createdbyuserinfo ->> 'username')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN trim(pi.createdbyuserinfo ->> 'firstName')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN trim(pi.createdbyuserinfo ->> 'surname')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN concat(trim(pi.createdbyuserinfo ->> 'surname'), ', ', trim(pi.createdbyuserinfo ->> 'firstName'))
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.createdbyuserinfo ->> 'firstName'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.createdbyuserinfo ->> 'surname'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
                      ELSE concat(trim(pi.createdbyuserinfo ->> 'surname'), ', ', trim(pi.createdbyuserinfo ->> 'firstName'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
           END                                      AS createdbydisplayname,
           pi.lastupdatedbyuserinfo ->> 'username'  AS lastupdatedbyusername,
           pi.lastupdatedbyuserinfo ->> 'firstName' AS lastupdatedbyname,
           pi.lastupdatedbyuserinfo ->> 'surname'   AS lastupdatedbylastname,
           CASE
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN NULL
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN trim(pi.lastupdatedbyuserinfo ->> 'username')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN trim(pi.lastupdatedbyuserinfo ->> 'firstName')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN trim(pi.lastupdatedbyuserinfo ->> 'surname')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(pi.lastupdatedbyuserinfo ->> 'firstName'))
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
                      ELSE concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(pi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
           END AS lastupdatedbydisplayname,
           pi.status,
           CASE
                      WHEN 'POINT' = geometrytype(pi.geometry) THEN st_x(pi.geometry)
                      ELSE NULL
           END,
           CASE
                      WHEN 'POINT' = geometrytype(pi.geometry) THEN st_y(pi.geometry)
                      ELSE NULL
           END,
           ou.UID,
           ou.name,
           ou.code,
           ous.LEVEL,
           pi.geometry,
           coalesce(registrationou.UID,ou.UID),
           tei.UID,
           tei.geometry
FROM       programinstance pi
inner join program pr
ON         pi.programid=pr.programid
left join  trackedentityinstance tei
ON         pi.trackedentityinstanceid=tei.trackedentityinstanceid
AND        tei.deleted IS FALSE
left join  organisationunit registrationou
ON         tei.organisationunitid=registrationou.organisationunitid
inner join organisationunit ou
ON         pi.organisationunitid=ou.organisationunitid
left join  _orgunitstructure ous
ON         pi.organisationunitid=ous.organisationunitid
left join  _organisationunitgroupsetstructure ougs
ON         pi.organisationunitid=ougs.organisationunitid
AND        (
                      cast(date_trunc('month', pi.enrollmentdate) AS DATE)=ougs.startdate
           OR         ougs.startdate IS NULL)
left join  _dateperiodstructure dps
ON         cast(pi.enrollmentdate AS DATE)=dps.dateperiod
WHERE      pr.programid=36633
AND        pi.organisationunitid IS NOT NULL
AND        pi.lastupdated <= '2024-01-05T18:32:47' a[
INSERT INTO analytics_enrollment_temp_kieuz3dtepm
            (
                        "uidlevel1",
                        "uidlevel2",
                        "uidlevel3",
                        "uidlevel4",
                        "uidlevel5",
                        "uidlevel6",
                        "uidlevel7",
                        "daily",
                        "weekly",
                        "weeklywednesday",
                        "weeklythursday",
                        "weeklysaturday",
                        "weeklysunday",
                        "biweekly",
                        "monthly",
                        "bimonthly",
                        "quarterly",
                        "quarterlynov",
                        "sixmonthly",
                        "sixmonthlyapril",
                        "sixmonthlynov",
                        "yearly",
                        "financialapril",
                        "financialjuly",
                        "financialoct",
                        "financialnov",
                        "XlWhhCvkNhv",
                        "plbdIxS05ws",
                        "g3QoANebtvt",
                        "NUuUjfCQqj3",
                        "P9lfonpL2dg",
                        "X6HKRWOowJw",
                        "JfKaih9Kvrb",
                        "CqooW7qp7Lh",
                        "pi",
                        "enrollmentdate",
                        "incidentdate",
                        "completeddate",
                        "lastupdated",
                        "storedby",
                        "createdbyusername",
                        "createdbyname",
                        "createdbylastname",
                        "createdbydisplayname",
                        "lastupdatedbyusername",
                        "lastupdatedbyname",
                        "lastupdatedbylastname",
                        "lastupdatedbydisplayname",
                        "enrollmentstatus",
                        "longitude",
                        "latitude",
                        "ou",
                        "ouname",
                        "oucode",
                        "oulevel",
                        "pigeometry",
                        "registrationou",
                        "tei",
                        "teigeometry"
            )
SELECT     ous."uidlevel1",
           ous."uidlevel2",
           ous."uidlevel3",
           ous."uidlevel4",
           ous."uidlevel5",
           ous."uidlevel6",
           ous."uidlevel7",
           dps."daily",
           dps."weekly",
           dps."weeklywednesday",
           dps."weeklythursday",
           dps."weeklysaturday",
           dps."weeklysunday",
           dps."biweekly",
           dps."monthly",
           dps."bimonthly",
           dps."quarterly",
           dps."quarterlynov",
           dps."sixmonthly",
           dps."sixmonthlyapril",
           dps."sixmonthlynov",
           dps."yearly",
           dps."financialapril",
           dps."financialjuly",
           dps."financialoct",
           dps."financialnov",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36597) AS "XlWhhCvkNhv",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36599) AS "plbdIxS05ws",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36592) AS "g3QoANebtvt",
           (
                  SELECT cast(value AS timestamp)
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36595
                  AND    value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$') AS "NUuUjfCQqj3",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36593) AS "P9lfonpL2dg",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36598) AS "X6HKRWOowJw",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36594) AS "JfKaih9Kvrb",
           (
                  SELECT value
                  FROM   trackedentityattributevalue
                  WHERE  trackedentityinstanceid=pi.trackedentityinstanceid
                  AND    trackedentityattributeid=36596) AS "CqooW7qp7Lh",
           pi.UID,
           pi.enrollmentdate,
           pi.incidentdate,
           CASE pi.status
                      WHEN 'COMPLETED' THEN pi.enddate
           END,
           pi.lastupdated,
           pi.storedby,
           pi.createdbyuserinfo ->> 'username'  AS createdbyusername,
           pi.createdbyuserinfo ->> 'firstName' AS createdbyname,
           pi.createdbyuserinfo ->> 'surname'   AS createdbylastname,
           CASE
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN NULL
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN trim(pi.createdbyuserinfo ->> 'username')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN trim(pi.createdbyuserinfo ->> 'firstName')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN trim(pi.createdbyuserinfo ->> 'surname')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') = '' THEN concat(trim(pi.createdbyuserinfo ->> 'surname'), ', ', trim(pi.createdbyuserinfo ->> 'firstName'))
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.createdbyuserinfo ->> 'firstName'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
                      WHEN coalesce(trim(pi.createdbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.createdbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.createdbyuserinfo ->> 'surname'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
                      ELSE concat(trim(pi.createdbyuserinfo ->> 'surname'), ', ', trim(pi.createdbyuserinfo ->> 'firstName'), ' (', trim(pi.createdbyuserinfo ->> 'username'), ')')
           END                                      AS createdbydisplayname,
           pi.lastupdatedbyuserinfo ->> 'username'  AS lastupdatedbyusername,
           pi.lastupdatedbyuserinfo ->> 'firstName' AS lastupdatedbyname,
           pi.lastupdatedbyuserinfo ->> 'surname'   AS lastupdatedbylastname,
           CASE
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN NULL
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN trim(pi.lastupdatedbyuserinfo ->> 'username')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN trim(pi.lastupdatedbyuserinfo ->> 'firstName')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN trim(pi.lastupdatedbyuserinfo ->> 'surname')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') = '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(pi.lastupdatedbyuserinfo ->> 'firstName'))
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
                      WHEN coalesce(trim(pi.lastupdatedbyuserinfo ->> 'surname'), '') <> ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'firstName'), '') = ''
                      AND        coalesce(trim(pi.lastupdatedbyuserinfo ->> 'username'), '') <> '' THEN concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
                      ELSE concat(trim(pi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(pi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(pi.lastupdatedbyuserinfo ->> 'username'), ')')
           END AS lastupdatedbydisplayname,
           pi.status,
           CASE
                      WHEN 'POINT' = geometrytype(pi.geometry) THEN st_x(pi.geometry)
                      ELSE NULL
           END,
           CASE
                      WHEN 'POINT' = geometrytype(pi.geometry) THEN st_y(pi.geometry)
                      ELSE NULL
           END,
           ou.UID,
           ou.name,
           ou.code,
           ous.LEVEL,
           pi.geometry,
           coalesce(registrationou.UID,ou.UID),
           tei.UID,
           tei.geometry
FROM       programinstance pi
inner join program pr
ON         pi.programid=pr.programid
left join  trackedentityinstance tei
ON         pi.trackedentityinstanceid=tei.trackedentityinstanceid
AND        tei.deleted IS FALSE
left join  organisationunit registrationou
ON         tei.organisationunitid=registrationou.organisationunitid
inner join organisationunit ou
ON         pi.organisationunitid=ou.organisationunitid
left join  _orgunitstructure ous
ON         pi.organisationunitid=ous.organisationunitid
left join  _organisationunitgroupsetstructure ougs
ON         pi.organisationunitid=ougs.organisationunitid
AND        (
                      cast(date_trunc('month', pi.enrollmentdate) AS DATE)=ougs.startdate
           OR         ougs.startdate IS NULL)
left join  _dateperiodstructure dps
ON         cast(pi.enrollmentdate AS DATE)=dps.dateperiod
WHERE      pr.programid=36633
AND        pi.organisationunitid IS NOT NULL
AND        pi.lastupdated <= '2024-01-05T18:32:47'
AND        pi.incidentdate IS NOT NULL
AND        pi.deleted IS FALSE ];d pi.incidentdate IS NOT NULL
AND
pi.deleted
IS
  FALSE ]; 
  
  nested exception is org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "0000-11-30"`

I have searched for that date - I cant find it.

19

sorry i am unable to identify the solution here, is it to upgrade or something? if it is so, i have it on 2.39.9 as well as 2.40.6. so whats the way forward now?
i had previously opened it here