Why Oracle Apex Date picker range does not show data in the range of 1 month?

Question

In my Oracle Apex app, I have created a date picker range to view statistics on the dashboard in the chosen period of time.

As you can see no data is displayed in the range from 18th March to 18th April while there is data in the system that was created on the 8th, 9th, 10th, 11th, 12th, 14th, 15th, 16th, and 17th of April. Also in some other ranges chart statistics are represented incorrectly.

The SQL query used for charts:

SELECT 'Working Equipment' label, count(TAG_IDENTIFIER) FROM   LOGS_HISTORY
WHERE  MODE__ = 'Working'
AND    TO_CHAR(TIME_, 'DD-MON-YYYY HH:MIPM') between :P16_DATE_FROM and :P16_DATE_TO
union all
SELECT 'Standby Equipment' label, count(TAG_IDENTIFIER) FROM   LOGS_HISTORY
WHERE  MODE__ = 'Standby'
AND    TO_CHAR(TIME_, 'DD-MON-YYYY HH:MIPM') between :P16_DATE_FROM and :P16_DATE_TO

Here you can see that range is based on range between date picker page items

I have also created dynamic action to refresh charts based on Page Item.

Important to mention is that the chart works for some other ranges:

Please help I am braking my head over this.

Answer 1

Decision to use a "conversion" function is correct; though, you should move it to the right side because - if there's an index on the TIME_ column, TO_CHAR applied to it will cause index NOT to be used.

So:

select ...
from ...
where time_ beween to_date(:P16_DATE_FROM, 'dd-mon-yyyy hh:mi pm', 'nls_date_language=english')
               and to_date(:P16_DATE_TO  , 'dd-mon-yyyy hh:mi pm', 'nls_date_language=english')
        

I used additional parameter - nls_date_language because month names can be tricky if NLS settings aren't correctly set. It is safer to use digits only.

---

Also, did you make sure that query you used really returns some data when executed outside of Apex' Charts (but in e.g. SQL Developer)? I know you said that data exist, but - did you actually check it?