Created date vs execution date

Good day to you all,

Please note, I have found some discrepancies on an event report and when I am trying to analyse it, I found DHIS2 uses 2 dates inside the database viz “created” and “executiondate”.

createddate

Unfortunately, I am failing to understand how they are generated by DHIS2. I am lost because:

  1. The event report with specific parameters gives me 150 records.
  2. When I execute the below query using “executiondate” I get 112 records.

select tedv.created,psi.executiondate, tedv.* from trackedentitydatavalue tedv
inner join programstageinstance psi on psi.programstageinstanceid = tedv.programstageinstanceid
inner join programinstance pi on pi.programinstanceid = psi.programinstanceid
where dataelementid = 847 and lower(value) like ‘%ref%’ and psi.programstageid in (249228)
and psi.executiondate >= ‘2018-10-01’ and psi.executiondate <= ‘2018-12-31’

  1. When I execute the same query using “created” I get 112 records too.

Either way, I am not getting that 150 records I get on the Event Reports side. Can anyone kindly help me to clarify the “created” and “executiondate”. Especillay I want to know which one is associated with the “Event Date”, which is used to filter the period on the event reports.

Thank you in advance for your support.

1 Like

Hi there @MSP!
The date referred to as “event date” in the user interface is the same as the “executiondate” column in the programstageinstance table.

Your assumption is correct that the event report should contain the same number of events as you would find in the database - filtering on execution date. There is however a number of possible reasons why the result of this specific query might be different.
When going to data administration and triggering generation of analytics, the data from programstageinstance and related tables are flattened out and inserted into a table named analytics_event_(YEAR)_(PROGRAM). The analytics_event_ tables is the ones that is actually used in event reports and other analytics, not the programstageinstance table. Since the number in event reports is higher than the numbers in the programstageinstance table, I would try using left joins - maybe one of your joins is removing some rows that would be returned if you did the same query on the analytics_event_ table?
Also, make sure to run analytics if you havent already.
What do you get if you query the analytics_event_ table?

Would love to hear from you on wether you figure out what causes the difference - if we cannot find an explanation this must be investigated as a bug report.

Best regards,
Markus

2 Likes

Thank you @Markus for your swift reply.

Let me analyse further with left join as well as on the analytics table and update back.

2 Likes

Hi @MSP,

I’m guessing that the only left join you’ll need is to the trackedentitydatavalue table (which is currently your first table). DHIS2 will have records for all of the other tables if an event is created, but it will only create a record in the trackedentittydatavalue table for those data elements that are then populated by the user - if a data element is left blank during data capture, then there won’t be a record for it in this table.

Cheers, Sam.

2 Likes