Azure DevOps 工作项中日期之间的天数(按查询)

问题描述

我是 Azure DevOps 服务用户.
是否有任何方法可以通过 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.

我试过 Activated Date >[字段] 创建日期 + 5 但结果是
TF51005:查询引用了不存在的字段.错误是由 Created Date + 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、首先可以使用工作项wiql api 来查询那些[Microsoft.VSTS.Common.ActivatedDate] > 的项[System.CreatedDate] 获取工作项的id

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、然后可以使用work item list api 列出上述步骤查询的那些工作项的字段详细信息.

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

3、最后使用powershell脚本过滤那些在Activated Date和Created Date之间持续时间超过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}，请查看 此处 获取个人访问令牌以在 API 调用下方进行身份验证

# 查询那些 [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

# 过滤那些在 Activated Date 和 Created Date 之间的持续时间超过 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 工作项中日期之间的天数(按查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！