Dataset: Issues & Pull Requests
Entity: Issues
Field ID: workflow_timeline
Type: List
Description: The list of status events associated with the workflow_status field. This field has a complex structure (list of objects). A series of operators are provided to help extract useful information from this field:
TIMELINE_DURATION: Returns the total duration, in seconds, spent in a given (list of) timeline event(s).
TIMELINE_FIRST_START_AT: Returns the first start timestamp (entry timestamp) for a (list of) timeline event(s).
TIMELINE_FIRST_END_AT: Returns the first end timestamp (exit timestamp) for a (list of) timeline event(s).
TIMELINE_LAST_START_AT: Returns the last start timestamp (entry timestamp) for a (list of) timeline event(s).
TIMELINE_LAST_END_AT: Returns the last end timestamp (exit timestamp) for a (list of) timeline event(s).
Source: Calculated
Transformation logic:
Pull requests: this field is not applicable and will always return an empty array
[].Issues: Use the historical events/changelog/audit log on issues to create one event per change of
workflow_status. See the section Structure of the workflow_timeline field for a more detailed explanation of the data model.
From: |
|
Github | N/A |
Gitlab | N/A |
Bitbucket | N/A |
JIRA (Issues) | Status transition events from the changelog API |
ClickUp (Issues) | N/A |
Trello (Issues) | N/A |
Structure of the workflow_timeline field:
The workflow_timeline field is an array of timeline events. Each timeline event represents time spent in a given workflow_status (e.g. the To Do or In Progress workflow statuses).
Each event entry has the following fields:
name: The name of the workflow status
name_id: The id of the workflow status in the source application
start_at: The datetime on which the issue entered the workflow status
end_at: The datetime on which the issue left the workflow status
Timeline events can be open or closed. An event is open when the item is currently in that status (e.g. the issue is currently in the workflow status In Progress). Open events have a start_at but no end_at. On the other side, closed events describe states that happened in the past and have both a start_at and an end_at.
The workflow_timeline field can be displayed in a custom formula and looks like this:
[
{
"name": "To Do",
"end_at": "2024-01-15T14:04:40Z",
"name_id": "10002",
"start_at": "2023-10-07T13:39:19Z"
},
{
"name": "In Progress",
"end_at": "2024-01-25T14:10:06Z",
"name_id": "10003",
"start_at": "2024-01-15T14:04:40Z"
},
{
"name": "In Review",
"end_at": "2024-01-30T14:10:10Z",
"name_id": "10004",
"start_at": "2024-01-25T14:10:06Z"
},
{
"name": "In Progress",
"end_at": "2024-02-05T14:10:06Z",
"name_id": "10003",
"start_at": "2024-01-30T14:10:10Z"
},
{
"name": "In Review",
"end_at": "2024-02-10T14:10:10Z",
"name_id": "10004",
"start_at": "2024-02-05T14:10:06Z"
},
{
"name": "Staging",
"end_at": null,
"name_id": "10005",
"start_at": "2024-02-10T14:10:10Z"
}
]
Using this example above, here is what each TIMELINE_* operator will return:
TIMELINE_DURATION(workflow_timeline, "In Progress")
// 2024-01-25T14:10:06Z - 2024-01-15T14:04:40Z
// + 2024-02-05T14:10:06Z - 2024-01-30T14:10:10Z
=> 1382722 (seconds)
TIMELINE_FIRST_START_AT(workflow_timeline, "In Progress")
=> 2023-01-15T14:04:40Z
TIMELINE_FIRST_END_AT(workflow_timeline, "In Progress")
=> 2024-01-25T14:10:06Z
TIMELINE_LAST_START_AT(workflow_timeline, "In Progress")
=> 2024-01-30T14:10:10Z
TIMELINE_LAST_END_AT(workflow_timeline, "In Progress")
=> 2024-02-05T14:10:06Z