dosubot[bot] commented on issue #7899:
URL:
https://github.com/apache/incubator-devlake/issues/7899#issuecomment-2289874557
<!-- Greeting -->
Hello, @jeffschaper! I'm here to help you with any bugs, questions, or
contributions you have for the repository. Let me know how I can assist you!
<!-- Answer -->
The issue you're experiencing with the "Last 2 days" filter in the Grafana
dashboard is likely due to the way the `__timeFilter` macro is applied in your
SQL queries. The `calendar_months` table's `month_timestamp` column is a
timestamp representing the first day of the month at midnight, which might not
align well with the "Last 2 days" filter.
To address this, ensure that the `__timeFilter` macro is correctly applied
to the `finished_date` column in your `_deployments` CTE and that the
`calendar_months` table is used appropriately for monthly aggregation. Here's a
revised version of your SQL query:
```sql
-- Metric 1: Number of deployments per month
with
_deployments as (
-- When deploying multiple commits in one pipeline, GitLab and BitBucket
may generate more than one deployment. However, DevLake consider these
deployments as ONE production deployment and use the last one's finished_date
as the finished date.
SELECT
date_format(deployment_finished_date, '%y/%m') as month,
count(cicd_deployment_id) as deployment_count
FROM
(
SELECT
cdc.cicd_deployment_id,
max(cdc.finished_date) as deployment_finished_date
FROM
cicd_deployment_commits cdc
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
and pm.`table` = 'cicd_scopes'
WHERE
pm.project_name in ($project)
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY
1
HAVING
$__timeFilter(max(cdc.finished_date))
) _production_deployments
GROUP BY
1
)
SELECT
cm.month,
case
when d.deployment_count is null then 0
else d.deployment_count
end as deployment_count
FROM
calendar_months cm
LEFT JOIN _deployments d on cm.month = d.month
WHERE
$__timeFilter(cm.month_timestamp)
```
Ensure that the `__timeFilter` macro is correctly applied to the
`finished_date` column in the `_deployments` CTE and that the `calendar_months`
table is used appropriately for monthly aggregation. This should help in
filtering the data correctly based on the selected time range
<sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/DORADebug.json)</sup><sup>[[2]](https://github.com/apache/incubator-devlake/blob/main/backend/core/models/migrationscripts/20230511_add_calendar_months.go)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
