Past issue status changes

Vanessa Friedrich

I would like to know how long a ticket was in a certain state in the past.

The tickets are all in status closed now and I would like to know how many of them took  longer than three weeks to be moved from open to "in progress" 

I was trying to use

fixVersion = x.x AND status was in (open) and status changed from "open" to "in progress" after -3w 

but with that query i get transition changes (time in source status) returned within seconds:

Usman_ju24nf

Hello @Vanessa Friedrich 

 

Jira Cloud does not natively support reporting how long an issue was in a specific status at a previous point in time using only JQL. While JQL offers operators like status CHANGED FROM ... TO ..., it cannot calculate elapsed time between transitions or show how long it remained in a status in the past

You can see similar topics - https://community.atlassian.com/forums/Jira-questions/JQL-Query-to-fetch-time-taken-from-one-status-to-another/qaq-p/2937046

The query you attempted retrieves issues where the status moved from "open" to "in progress" after a certain point. However, this checks when the transition occurred, not how long the ticket remained in "open" before moving to "in progress." This is why you see transition durations of just seconds, reflecting only the timing of the transition event, not the duration in the "open" state

A few limitations with Jira Cloud JQL

• JQL cannot calculate the duration between two separate status transitions or find issues that stayed in a status longer than a specific period before transitioning.

• Built-in JQL filters only allow you to see when transitions occurred, not the elapsed time between them.

• Time-in-status metrics are not available retroactively for closed issues unless a third-party add-on or automation was in place beforehand

Workaround

You can use marketplace apps that analyze issue history and calculate time spent in each status. Popular options include:

• Time in Status for Jira Cloud

• Timepiece - Time in Status
  These tools process historical data and can report on issues that took longer than three weeks in "open" before moving to "in progress"

Jira API 

• Use the Jira REST API to export the issue history. (GET https://your-domain.atlassian.net/rest/api/3/issue/{issueIdOrKey}/changelog)

• Write a script to parse each issue’s history and calculate the interval between entering "open" and "in progress."

• Filter for tickets where this interval exceeds three weeks

Jira Automation for Future Issues

• Set up automation rules that record timestamps in custom fields when a ticket enters or exits statuses.

• Calculate and store the duration in "open" for future analysis