'Portfolios with reviewers' report date calculation error

[Dianne Tennent]

Reports > Configure report > Dates: 14th March - 23rd April, 2022, Report type: Portfolios with reviewers

Tested in both mysql and postgres

When the dates in the institution_data table are within a certain range, all the information does not appear on the report.

Test cases:
Given there are two entry sets in the institution_data table (created by cron run each Monday) on these dates ('ctime') and the reporting time period is set to be between 14th March - 23rd April, 2022, in this case we only get data from the 18th April (despite the paginator counting 2 results):
2022-04-04 14:22:57 (Monday)
2022-04-18 14:22:57 (Monday)

AND
2022-04-11 14:22:57 (Monday)
2022-04-18 14:22:57 (Monday)

However, for the same reporting period, we get BOTH results when the entries in the table are the following:
2022-03-21 14:22:57 (Monday)
2022-04-18 14:22:57 (Monday)

And..

2022-03-28 14:22:57 (Monday)
2022-04-18 14:22:57 (Monday)

The theory is that it's something to do with Daylight Savings time, which comes into effect on 2nd of April. Specifically, line 2450 in htdocs/lib/statistics.php, where the $from date is incremented by a week with $from = $from + (7 * 24 * 60 * 60) to set the $daterange

[Kristina Hoeppner (kris-hoeppner)]

What are possible solutions?

[Dianne Tennent (dianne-t)]

It might be able to be solved by looking into how dates are generated in php. There are some cases where an arbitrary hour is assigned to a date when it isn't given. This, combined with calculating times relative to UTC may be what's causing the incorrect date to be generated. So some more precision with this might help. However, it's obviously quite an edge case.

[Kristina Hoeppner (kris-hoeppner)]

We should use an existing function rather than attempting to do the calculation ourselves.