vshulga-sift opened a new issue, #8144: URL: https://github.com/apache/incubator-devlake/issues/8144
### 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 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: 1. $__timeFilter(pr.created_date) AND 2. created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH) 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);` | Query/Date range | Result | | ------------- | ------------- | | SELECT NOW(); | 2024-10-15 13:25:47 | | Last 5 minutes | 2024-11-01 12:25:14 | | Last 15 minutes | 2024-11-01 12:16:14 | | Last 30 minutes | 2024-11-01 12:01:36 | | Last 1 hour | 2024-11-01 11:32:15 | | Last 3 hour | 2024-11-01 09:32:37 | | Last 6 hour | 2024-11-01 06:33:02 | | Last 12 hour | 2024-11-02 00:33:26 | | Last 24 hour | 2024-11-01 12:33:46 | | Last 2 days | 2024-11-01 12:34:09 | | Last 7 days | 2024-11-01 12:34:33 | | Last 30 days | 2024-10-01 13:35:08 | | Last 90 days | 2024-08-01 13:35:32 | | Last 6 month | 2024-05-01 13:35:55 | | Last 1 year | 2023-11-01 12:36:20 | | Last 2 year | 2022-11-01 12:36:42 | | Last 5 year | 2019-11-01 12:37:12 | | Yesterday | 2024-11-01 23:00:00 | | Day before yesterday | 2024-11-01 23:00:00 | | This day last week | 2024-11-01 23:00:00 | | Previous week | 2024-11-01 23:00:00 | | Previous month | 2024-09-02 00:00:00 | | Previous fiscal quarter | 2024-07-02 00:00:00 | | Previous year | 2023-01-02 00:00:00 | | Previous fiscal year | 2023-01-02 00:00:00 | | Today | 2024-11-01 23:00:00 | | Today so far | 2024-11-01 23:00:00 | | This week | 2024-11-01 23:00:00 | | This week so far | 2024-11-01 23:00:00 | | This month | 2024-10-02 00:00:00 | | This month so far | 2024-10-02 00:00:00 | | This year | 2024-01-02 00:00:00 | | This year so far | 2024-01-02 00:00:00 | | This fiscal quarter | 2024-10-02 00:00:00 | | This fiscal quarter so far | 2024-10-02 00:00:00 | | This fiscal year | 2024-01-02 00:00:00 | | This fiscal year so far | 2024-01-02 00:00:00 | 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 - Collect PRs data for the last 2 months - Open DORA Details - Lead Time for Changes Grafana dashboard - Select data range last 7 days or less ### Anything else _No response_ ### Version release-v1.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: dev-unsubscr...@devlake.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
