通过查询在Azure DevOps工作项中的日期之间的天数 [英] Days between dates in Azure DevOps work items by query

问题描述

我是Azure DevOps Services用户.
是否可以通过Azure Boards查询来计算工作项中日期之间的持续时间?
或者，日期之间是否有日期字段?

I am an Azure DevOps Services user.
Are there any ways to calculate the duration between dates in work items by Azure Boards query?
Or, are there any fields of days between dates?

例如，我想显示一个工作项列表，该工作项的激活日期"和创建日期"之间的持续时间超过5天.

For example, I would like to display a list of work items which duration between Activated Date and Created Date exceeds 5 days.

我尝试了激活日期>[字段]创建日期+ 5 ，但结果为
TF51005:查询引用了一个不存在的字段.该错误是由创建日期+ 5引起的.

我想知道是否只有支持的宏(如@Today或@StartOfDay)才能在查询编辑器中用于计算.
我阅读了以下官方参考，但找不到解决方法.

I wonder if only supported macros like @Today or @StartOfDay are able to be used for calculation in query editor.
I read the following official references but I could not reach a solution.

• 按日期或当前迭代进行查询
• 查询字段，运算符和宏
• 如果查询中没有解决方案，欢迎提出另一种想法.

  If there are no solutions by query, another ideas are welcome.

  推荐答案

  这不能通过查询完成.您将需要使用rest api作为解决方法.但这很复杂，可能无法达到与Query相同的效果.

  This cannot be done by query. You will need to use rest api as workaround. But it is complicated and might not achieve the same effect as Query.

  1，首先，您可以使用

  1, First you can use work item wiql api to query those items that [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate] to get the ids of workitems

  2，然后您可以使用

  2, Then you can use work item list api to list the fields details of those work items queried by above step.

  3，最后一次使用powershell脚本过滤那些在激活日期"和创建日期"之间的持续时间超过5天的工作项

  3, Last use powershell scripts to filter those work items whose duration between Activated Date and Created Date exceeds 5 days

  请在powershell脚本中检查以下示例:

  Please check below example in powershell scripts:

  对于{PAT}，请检查

  For {PAT} please check here to get a Personal Access Token to Authenticate below API calling

  #查询[Microsoft.VSTS.Common.ActivatedDate]> [System.CreatedDate]的那些项，并获取工作项的ID

  # query those items that [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate] and get the ids of workitems

  $qurl =  "https://dev.azure.com/{org}/{proj}/_apis/wit/wiql?api-version=5.1"
  
  $WIQL_query = "Select [System.Id], [System.Title], [System.State],[Microsoft.VSTS.Common.ActivatedDate],[System.CreatedDate] From WorkItems Where [Microsoft.VSTS.Common.ActivatedDate] > [System.CreatedDate]"
  $body = @{ query = $WIQL_query }
  $bodyJson=@($body) | ConvertTo-Json
  
  $pat = {PAT}
  
  $base64AuthInfo= [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($pat)"))
  
  $result = Invoke-RestMethod -Uri $qurl -Headers @{authorization = "Basic $base64AuthInfo"} -Method post -ContentType "application/json" -Body $bodyJson
  

  #获取工作室ID

  $ids = $result.workItems | select id | foreach{ $_.id }
  $id= '{0}' -f ($ids -join ",")
  

  #使用工作项列表api列出这些工作项的字段详细信息

  # use work item list api to list the fields details of those work items

  $url = "https://dev.azure.com/{ORG}/{PROJ}/_apis/wit/workitems?ids=$($id)&api-version=5.1"
  
  $result1 = Invoke-RestMethod -Uri $url -Headers @{authorization = "Basic $base64AuthInfo"} -Method get
  

  #过滤激活日期和创建日期之间的持续时间超过5天的那些工作项.

  # Filter those workitems whose duration between Activated Date and Created Date exceeds 5 days.

  $result1.value.fields | where {[datetime]$_.'Microsoft.VSTS.Common.ActivatedDate' -gt ([datetime]$_.'System.CreatedDate').AddDays(5)} 
  

  希望上面有帮助！

  这篇关于通过查询在Azure DevOps工作项中的日期之间的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！