[GitHub] [incubator-devlake] Startrekzky opened a new issue, #3224: [Bug][DORA] bigint unsigned value is out of range mysql

[GitBox <gi...@apache.org>]

Startrekzky opened a new issue, #3224:
URL: https://github.com/apache/incubator-devlake/issues/3224

   ### 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
   
   When calculating DORA metric - lead time for changes, I used the query that did subtraction between issues.lead_time_minutes. MySQL prompted error 'bigint unsigned value is out of range mysql'.
   
   I fixed it by converting it with `cast(lead_time_minutes as signed) as lead_time_minutes`. 
   
   ```
   -- Metric 3: Median time to restore service 
   with _incidents as (
   -- get the incidents created within the selected time period in the top-right corner
   	SELECT
   		cast(lead_time_minutes as signed) as lead_time_minutes
   	FROM
   		issues
   	WHERE
   		type = 'INCIDENT'
   		and $__timeFilter(created_date)
   ),
   
   _median_mttr as (
   	SELECT 
   		x.lead_time_minutes as med_time_to_resolve
   	from _incidents x, _incidents y
   	WHERE x.lead_time_minutes is not null and y.lead_time_minutes is not null
   	GROUP BY x.lead_time_minutes
   	HAVING SUM(SIGN(1-SIGN(y.lead_time_minutes-x.lead_time_minutes)))/COUNT(*) > 0.5
   	LIMIT 1
   )
   
   SELECT 
   	case
   		WHEN med_time_to_resolve < 60  then "Less than one hour"
       WHEN med_time_to_resolve < 24 * 60 then "Less than one Day"
       WHEN med_time_to_resolve < 7 * 24 * 60  then "Between one day and one week"
       ELSE "More than one week"
       END as med_time_to_resolve
   FROM 
   	_median_mttr
   ```
   
   
   
   ### What you expected to happen
   
   I don't have to cast a field as signed.
   
   ### How to reproduce
   
   Run the SQL above.
   
   ### Anything else
   
   _No response_
   
   ### Version
   
   main
   
   ### 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: commits-unsubscribe@devlake.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [incubator-devlake] mindlesscloud closed issue #3224: [Bug][DORA] bigint unsigned value is out of range mysql

[GitBox <gi...@apache.org>]

mindlesscloud closed issue #3224: [Bug][DORA] bigint unsigned value is out of range mysql
URL: https://github.com/apache/incubator-devlake/issues/3224


-- 
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: commits-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org