Type: Function
Function: TIMELINE_BUSINESS_HOURS
Syntax: TIMELINE_BUSINESS_HOURS(timeline_field, event|event_list, [timezone="UTC"], [hour_start="09:00"], [hour_end="17:00"], [weekday_start=1], [weekday_end=5], [closed_only=false], [ignore_skipped=false])
Description: Returns a decimal number representing the business hours spent in a given (list of) timeline event(s). Event matching is case-insensitive. This operator combines the timeline iteration logic of TIMELINE_DURATION with the business duration calculation of BUSINESS_HOURS.
Full days of work are accounted for based on specified working hours (8 for the default range 09:00 to 17:00), not based on the 24-hour clock day. For instance, if an item spent two full business days in In Progress, the result will be 16 hours when using the default working hours.
If you change the working hours to be from 06:00 till 22:00 (16-hour day), then two full business days become 32 hours.
The range of days worked within the week is specified using the ISO8601 standard, where Monday is 1 and Sunday is 7.
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). The operator uses NOW() as an end_at for open events. To ignore open events and only calculate realized durations, set closed_only to true.
If no events match the provided list of events then the operator returns 0. This impacts the calculation of averages as the value (0) will be counted and artificially lower the resulting average. If you prefer to restrict the average to actual values, set ignore_skipped to true. When this mode is enabled the operator returns NULL instead of 0 when no events match.
The list of supported timezones is specified below.
Sample usage:
TIMELINE_BUSINESS_HOURS(workflow_timeline, "In Progress")returns the business hours spent in the workflow statusIn Progress. For items currently inIn Progress, the duration will be continuous (usingNOW()as end).TIMELINE_BUSINESS_HOURS(workflow_timeline, ["In Progress", "Staging"])returns the combined business hours spent inIn ProgressandStaging.TIMELINE_BUSINESS_HOURS(workflow_timeline, "In Progress", "CET")returns the business hours spent inIn Progress, with working hours evaluated in theCETtimezone.TIMELINE_BUSINESS_HOURS(workflow_timeline, "In Progress", "CET", "10:00", "14:00", 2, 4)returns the business hours spent inIn Progress, with a custom schedule of Tuesday to Thursday, 10:00 to 14:00 CET. Each full business day counts as4hours.TIMELINE_BUSINESS_HOURS(workflow_timeline, "In Progress", "UTC", "09:00", "17:00", 1, 5, true)returns the closed business hours spent inIn Progress. For items currently inIn Progress, the result will be zero since the event is open and the closed only mode is requested.TIMELINE_BUSINESS_HOURS(workflow_timeline, "In Progress", "UTC", "09:00", "17:00", 1, 5, false, true)returns the business hours spent inIn Progress. If no events match then the result will beNULLsince the ignore skipped mode is requested.
List of supported timezones:
Below is the list of timezone abbreviations supported by the timezone argument: ACST, ADT, AEST, AKDT, AST, AWST, BST, CAT, CDT, CEST, CET, CST, ChST, EAT, EDT, EEST, EET, EST, GMT, HDT, HKT, HST, IDT, IST, JST, KST, MDT, MSK, MST, NDT, NZST, PDT, PKT, PST, SAST, SST, UTC, WAT, WEST, WIB, WIT, WITA
For a complete list of all supported "named" timezones (e.g. Europe/Amsterdam), see the following help article.