Registration list shows inaccurate information due the way timezones are managed

[makelicious]

Managing time selections and timezones in registrations

Current registration filtering by time has multiple different issues. Some of them happen constantly, some of them are more rare to catch without proper test set. Here are few of the bugs that are easiest to replicate. At the bottom, I elaborate on the causes of the issue and possible approaches we could take.

Describe the bug
When viewing registrations over time, table renders inaccurate results.
When interacting with month selection picker, table will show wrong results.

Which feature of OpenCRVS your bug concern?
Application feature: Performance - Registrations

Bug 1: Default view (Last 12 months) shows 13 months. 13th month values are wrong.

To Reproduce
Steps to reproduce the behaviour:

1. Login as a system admin user
2. Go to 'Performance'
3. Under 'Registrations' click 'View' on 'Total' row
4. Count the rows rendered (13 instead of 12)
5. Add birth registrations on the 13th month (August 2023 as of writing)
6. Shows 0 instead of the actual number.

Expected behaviour

• When user select 'Last 12 months' they see last 12 months
• When 13th month is shown, it has 0 registrations

Actual behaviour

• When user select 'Last 12 months' they see last 13 months
• When 13th month is shown, it should have the actual number of registrations

---

Bug 2: Reselecting month in month-picker yields different results than the initial query

To Reproduce
Steps to reproduce the behaviour:

1. Login as a system admin user
2. Go to 'Performance'
3. Under 'Registrations' click 'View' on 'Total' row
4. Check values on the first row
5. Click 'Last 12 months'
6. On right side of the picker, click 'April' and then back to current month 'August'
7. Click 'Select'
8. Check values on the first row

Expected behaviour

• Values should be the same when selection is the same

Actual behaviour

• Values change when same selection is done twice

Bug 3: Table claims no data on different ranges

To Reproduce
Steps to reproduce the behaviour:

1. Login as a system admin user
2. Go to 'Performance'
3. Under 'Registrations' click 'View' on 'Total' row
4. Take a row that has registrations (May in the example) and the previous one does not
5. Select a range that includes it (April-June)
6. Ensure value is correct
7. Select range ending which ends on the selected month (April-May)
8. Check the values

Expected behaviour

• Values should be the same for months despite of range

Actual behaviour

• Table results differ when range differs for same months

Bug 4: Table claims no data on different ranges when it's visible on others

To Reproduce
Steps to reproduce the behaviour:

1. Login as a system admin user
2. Go to 'Performance'
3. Under 'Registrations' click 'View' on 'Total' row
4. Take a row that has registrations (May in the example) and the previous one does not
5. Select a range that includes it (April-June)
6. Ensure value is correct
7. Select range ending which ends on the selected month (April-May)
8. Check the values

Expected behaviour

• Values should be the same for months despite of range

Actual behaviour

• Table results differ when range differs for specific months

OpenCRVS Core Version: v1.6.0

Country Configuration Version: Farajaland v1.6.0

Desktop (please complete the following information):

• OS: [e.g. Mac OSX]
• Browsers: Chrome, Firefox
• Version Latest

Additional context
Query sent: getRegistrationsListByFilter

{
  event: "BIRTH",
  filterBy: "by_time",
  size: 10,
  skip: 0,
  timeEnd: "2024-08-19T20:59:59.999Z",   // My local time is taken into account
  timeStart: "2023-08-31T21:00:00.000Z"  // My local time is taken into account
}

packages/metrics/src/features/metrics/metricsGenerator.ts:

1. When dealing with time ranges, the usual convention is [inclusiveStart, exclusiveEnd). Range function follows it, too. Technically, even if unlikely, we will exclude registration which happened at 2024-08-19T20:59:59.999Z.
2. Values are aggregated by month. First month has the timezone offset (2023-08-31T21:00:00.000Z) This causes the inclusion of the 13th group for yearly queries. The following months do not have the offset grouping. This would cause the aggregation to not apply the local timezone for rest of the months.

// Payload generated for influx
from(bucket: "ocrvs")
|> range(start: 2023-08-31T21:00:00.000Z, stop: 2024-08-19T20:59:59.999Z) // 1.
|> filter(fn: (r) => r._measurement == "birth_registration")
|> filter(fn: (r) => r._field == "ageInDays")
|> group(columns: ["timeLabel", "eventLocationType"])
|> aggregateWindow(every: 1mo, fn: count, timeSrc: "_start") // 2.
|> sort(columns: ["_time"], desc: true)
|> rename(columns: {_value: "total"})

Possible fixes
Assumptions:

• We want to show same results for each month independent of local timezone. (People should have same results for registrations in May 2024 for Farajaland, even if they are in different timezones)
• When requesting “Last 30 days” we want full days. (Currently you get results 30 days from your exact time. (e.g. if you send the request 3pm, registration at 2pm on the 30th day won’t show up in the results)
• Solving for multiple timezones happens later

Approaches:

• Use relative searches when possible (No timezones needed)
• Find a way to ensure and enforce relative timezoneoffset, add it to query or to backend
• Simplify timestamps, use yyyy-mm-dd instead of including time

[rikukissa]

@marcio-opencrvs I'm blocking this until we know if we want to keep the built-in performance views in the product. Otherwise it's a bit of time wasted fixing these related issues