dosubot[bot]
commented
1 month ago Hey @vshulga-sift, I'm here to help you with any bugs, questions, or contributions! Let's work together to solve this issue.
The issue you're encountering is related to the second condition in the SQL query for filtering created_date in the Grafana dashboard. This condition is designed to ensure that only pull requests created after the start of the month following the __timeFrom() date are considered. However, as you've observed, this can lead to incorrect filtering, especially for date ranges less than one month, as it effectively compares created_date with a future date.
The second condition is:
created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)This logic is intended to filter out pull requests that were created before the start of the next month from the __timeFrom() date. However, it seems to be causing issues with shorter date ranges, as it excludes PRs that should be included based on the selected time range.
To address this, you might consider revising or removing this condition to ensure that the created_date is filtered correctly according to the selected date range in Grafana. This would help in accurately calculating the average metrics for the specified period.
To continue talking to Dosu, mention @dosu.
Search before asking
What happened
We found the issue on Grafana DORA Details - Lead Time for Changes Dashboard. We see the list of PRs on PRs Detail list but do not see any Average metrics calculated. This happened only when we chose date range less or equal for Last 7 days.
After some investigation we found that Average PR Cycle Time has 2 conditions for created_date:
The first condition works as expected, however, it seems that second condition is wrong. It gets the first day of current month and add to it interval in 1 month. As result we do comparison of pr created_date with the beginning of the next month if we choose date range less than 1 month. For 1month+ intervals we have some data as for case of the last month we compare created_date with the beginning of current month. But this calculation is also inaccurate.
Example of results for different date time range selection and query
SELECT DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH);Is second condition needed at all?
What do you expect to happen
created_date is filtered by correct data range selection in Grafana
How to reproduce
Anything else
No response
Version
release-v1.0
Are you willing to submit PR?
Code of Conduct