Time in Status

How long do your work items sit in each status? Time in Status analysis answers questions like:

• Where do work items spend most of their time — and where are the bottlenecks?
• How long does review or testing really take for different work item types?
• Which items have been stuck in their current status the longest?
• Are we meeting our SLAs for response and resolution times?

Unlike dedicated single-purpose apps, Advanced Reports gives you full SQL access to the underlying status-change data. Any breakdown you can express in SQL — per status, per work item, per type, per assignee, per project, percentiles, trends over time — is a single query away, and the results can be turned into charts and embedded in reports.

How it works

When Sync change history is enabled in the Database Configuration, every field transition is stored in the histories table (see the database schema):

Time in Status analysis boils down to filtering this table on field_id = 'status' and aggregating seconds_in_state.

Time in each status, per work item

The fundamental query: total time each work item spent in every status, including time accrued so far in the current status.

Example Result:

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    i."key" AS work_item_key,
    s.name AS status_name,
    ROUND(SUM(COALESCE(h.seconds_in_state, EPOCH(NOW() - h.date))) / 86400.0, 1) AS days_in_status
FROM histories h
JOIN work_items i ON h.work_item_id = i.id
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
GROUP BY i."key", s.name
ORDER BY i."key", days_in_status DESC;

Time in Status report (statuses as columns)

The classic Time in Status report: one row per work item, one column per status. DuckDB's PIVOT (opens in a new tab) statement creates the status columns dynamically — no need to list your statuses by hand.

Example Result:

-- Note: Ensure the 'status' field is selected in the Database Configuration
PIVOT (
    SELECT
        i."key" AS work_item_key,
        s.name AS status_name,
        COALESCE(h.seconds_in_state, EPOCH(NOW() - h.date)) / 86400.0 AS days_in_status
    FROM histories h
    JOIN work_items i ON h.work_item_id = i.id
    JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
)
ON status_name
USING ROUND(SUM(days_in_status), 1)
GROUP BY work_item_key
ORDER BY work_item_key;

Average and median time per status

Where does work wait the longest? Averages are easily skewed by outliers, so include the median (and add higher percentiles if you need them — see Bottleneck Detection for a 95th-percentile variant).

Example Result:

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    s.name AS status_name,
    ROUND(AVG(h.seconds_in_state) / 86400.0, 1) AS avg_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY h.seconds_in_state) / 86400.0, 1) AS median_days,
    COUNT(*) AS transitions
FROM histories h
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
WHERE h.seconds_in_state IS NOT NULL
GROUP BY s.name
ORDER BY avg_days DESC;

Items stuck in their current status

Find the work items that have been sitting in their current status the longest — ideal for a "needs attention" report or dashboard.

Example Result:

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    i."key",
    s.name AS status_name,
    ROUND(EPOCH(NOW() - h.date) / 86400.0, 1) AS days_in_current_status
FROM histories h
JOIN work_items i ON h.work_item_id = i.id
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
WHERE h.seconds_in_state IS NULL -- current status only
ORDER BY days_in_current_status DESC
LIMIT 20;

Cycle time from status categories

Instead of tracking individual statuses, you can aggregate by Jira's status categories (To Do / In Progress / Done) via the status_categories lookup table. Summing the time spent in the "In Progress" category gives a robust cycle time measure even when teams use different workflow statuses.

Example Result:

-- Note: Ensure the 'status' field is selected in the Database Configuration
SELECT
    i."key" AS work_item_key,
    ROUND(SUM(h.seconds_in_state) / 86400.0, 1) AS cycle_time_days
FROM histories h
JOIN work_items i ON h.work_item_id = i.id
JOIN statuses s ON h.field_id = 'status' AND h.value = s.id
JOIN status_categories sc ON sc.id = s.statusCategoryId
WHERE sc.name = 'In Progress'
    AND h.seconds_in_state IS NOT NULL
GROUP BY i."key"
ORDER BY cycle_time_days DESC;

Filtering and slicing

All of the queries above can be narrowed down with ordinary SQL. Common patterns:

• By project: add WHERE i.project_key = 'PROJ' (or join the projects table).
• By date range: add WHERE i.resolutiondate >= current_date - INTERVAL '90' DAY to analyse only recently completed items.
• By work item type: join issuetypes it ON it.id = i.issuetype and group by it.name — see Average Time in Status by Work Item Type.
• By assignee: join users u ON u.id = i.assignee and group by u.displayName.

Visualizing Time in Status

Any of these queries can be turned into a chart — for example, a stacked bar chart of the per-work-item query, with one bar per work item segmented by status, or a bar chart of average days per status.

You can also skip the SQL entirely and ask AI to build the analysis for you — the AI Assistant in the Insights editor or the AI Explorer (Advanced Edition) can generate both the query and the chart from a prompt like:

Show me a stacked bar chart of how many days each work item in project TP spent in each status.

The AI Explorer responds with key observations, the generated chart, and the underlying SQL and chart spec — and the result can be saved as an Insight for reuse in reports.

AI Explorer generating a Time in Status stacked bar chart