Function: TIMELINE_DURATION
Syntax: TIMELINE_DURATION(timeline_field, event|event_list, [strict_mode: true|false(default)])
Description: Returns the total duration, in seconds, spent in a given (list of) timeline event(s). This operator is currently used with the workflow_timeline field to calculate the time spent in a given workflow status (= "time spent in a column"). More timeline fields will be made available in the future.
Timeline events can be open or closed. An event is open when the item is currently in that state (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.
See the section Structure of timeline fields for a detailed example of timeline.
The operator uses NOW() as an end_at for open events to calculate the duration. It means the duration is continuous for states that are current to a given item. To ignore open events and only calculate realized durations, pass true as a third argument to enable the strict mode
Sample usage:
TIMELINE_DURATION(workflow_timeline, "In Progress")returns the duration, in seconds, spent in the workflow statusIn Progress. For items that are currently in theIn Progressworkflow status (open event), the duration will be continuous.TIMELINE_DURATION(workflow_timeline, "In Progress", true)returns the strict duration, in seconds, spent in the workflow statusIn Progress. For items that are currently in theIn Progressworkflow status (open event), the duration will be zero since the event is open and the strict mode (no open events) is requested.TIMELINE_DURATION(workflow_timeline, ["In Progress", "Staging"]) / DAY()returns the combined duration in days spent in the workflow statusesIn ProgressandStaging.
Structure of timeline fields:
Timeline fields (e.g. workflow_timeline) are arrays of timeline events. Each timeline event represents a transient state between two transitions (e.g. time spent in the In Progress workflow status). TIMELINE_* operators are used to ease the manipulation of timeline events.
Timeline fields can be displayed in reports and look like this:
[
{
"name": "To Do",
"end_at": "2023-03-31T14:04:40Z",
"name_id": "10002",
"start_at": "2022-10-07T13:39:19Z"
},
{
"name": "In Progress",
"end_at": "2024-01-15T14:10:06Z",
"name_id": "10003",
"start_at": "2023-03-31T14:04:40Z"
},
{
"name": "In Review",
"end_at": "2024-01-15T14:10:10Z",
"name_id": "10004",
"start_at": "2024-01-15T14:10:06Z"
},
{
"name": "Staging",
"end_at": null,
"name_id": "10005",
"start_at": "2024-01-15T14:10:10Z"
}
]