[Bug][Grafana] PR Cycle TIme doesn't Respect Time Interval

[ldmoose]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

Working:

Not Working:

What do you expect to happen

I expect that when the time interval option in the board changes that the PR Cycle Time graph respects that change.

How to reproduce

1. Go to https://devlake.apache.org/livedemo/EngineeringLeads/EngineeringThroughputAndCycleTime
2. Change Time Interval to 'Week'
3. The Pr Cycle Time doesn't respect the change

Anything else

Problem is here: https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/EngineeringThroughputAndCycleTime.json#L1161

Specifically, in the main select statement, the first column definition (and the subsequent group by) should have INTERVAL -$interval(date(pr_issued_date))+1 DAY as the second function parameter in the DATE_ADD function call.

WITH _prs AS (
  SELECT pr.id
    , pr.created_date AS pr_issued_date
    -- convert null to 0 if a PR has no cycle_time to make sure cycle_time equals the sum of the four metrics below
    , COALESCE(prm.pr_cycle_time/60,0) AS cycle_time
FROM pull_requests pr
LEFT JOIN project_pr_metrics prm ON pr.id = prm.id
INNER JOIN project_mapping pm ON pr.base_repo_id = pm.row_id AND pm.table = 'repos'
WHERE $__timeFilter(pr.created_date)
AND pr.created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
AND pm.project_name in (${project:sqlstring}+'')
GROUP BY pr.id
  , pr.created_date
  , COALESCE(prm.pr_cycle_time/60,0)
)

SELECT 
  DATE_ADD(date(pr_issued_date), INTERVAL -DAYOFMONTH(date(pr_issued_date))+1 DAY) AS time
  , AVG(cycle_time) AS 'PR Cycle Time(h)'
FROM _prs
GROUP BY DATE_ADD(date(pr_issued_date), INTERVAL -DAYOFMONTH(date(pr_issued_date))+1 DAY)
ORDER BY time

Version

Latest: v0.19.0

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[abeizn]

@ldmoose Thanks a lot, we will fix it in v0.21.0-beta7.