jeffschaper opened a new issue, #7899: URL: https://github.com/apache/incubator-devlake/issues/7899
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar issues. ### What happened I filtered on the "Last 2 days" drop down in the Grafana dashboard and didn't see any metrics related to my application. ### What do you expect to happen I would expect to see metrics related to my application regardless of the time frame selected. ### How to reproduce 1. Make one or more commits in a new month 2. Wait at least three days into the month 3. Select the "Last 2 days" filter in the Grafana dashboard Important: The last two days should not overlap the beginning or end of a month. Make sure the "Last 2 days" are in the middle of the month. ### Anything else I'm not sure if this is a bug or a design decision. The SQL query appears to use the `BETWEEN` keyword in place of the `$__timeFilter` function. This results in the query looking something like this: ```sql 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 cm.month_timestamp BETWEEN '2024-07-09T15:15:33.784Z' AND '2024-07-11T15:15:33.784Z' ORDER BY cm.month; ``` The `calendar_months` table appears to be truncating the `month_timestamp` column, so the `WHERE` clause doesn't catch anything. Here's the full 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) ``` ### Version v0.19.0 ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
