Function: TIMELINE_DURATION
Syntax: TIMELINE_DURATION(timeline_field, event|event_list, [closed_only: 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 closed-only mode.
If no events match the provided list of events then the operator returns 0. This behavior impacts the calculation of averages as the value (0) will be counted and artificially lower the resulting average. This behavior may be desired if you consider that "skipping a step" should be counted positively in the resulting average. If you prefer to restrict the average to actual values you may pass true as the fourth argument to enable the null if none mode. When this mode is enabled the operator returns NULL instead of 0 when no events match the provided list of events.
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 closed 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 closed-only mode (no open events) is requested.TIMELINE_DURATION(workflow_timeline, "In Progress", false, true)returns the duration, in seconds, spent in the workflow statusIn Progress. If no events are associated with theIn Progressstatus then the returned duration will beNULLsince the null if none mode 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"
}
]