Query for date range between creation and status change

Question

Dear Community,

I haven't been able to find a solution for the following problem:

I want to find all tickets of type "Change Request" that needed more than 7 days from ticket creation to the first status change to status "Agreed".

This is how far I got:

issuetype = "Change Request" and status CHANGED TO "Agreed" after startOfDay("-7d")

this gives me all tickets that changed to "Agreed" in the last seven days before today. What I need now is something like created("-7d") instead of startOfDay("-7d"):

issuetype = "Change Request" and status CHANGED TO "Agreed" after created("-7d")

but this query does not work as the function "created(...)" does not exist.

I've also looked for a function that does date arithmetic e.g. dateRange(created, "-7d"), but no such luck.

Any help appreciated!

Thanks!

Peter

Summary of answers:

JQL has limited support for date arithmetics, and, with regard to my problem, you cannot calculate the difference between two status changes and use that difference in a comparison.

There are a few workarounds that do require some effort:

* Native Jira: create a new field for the time difference that automatically gets updated on status change. This field can then be used in a query.
* Using apps from Atlassian Marketplace, e.g.* Time in Status
* Time Metrics Tracker| Time Between Statuses
* JXL for Jira

More details below.

Hannes Obweger · Accepted Answer

Hi @Peter Neuhaus,

welcome to the community!

I don't actually believe that this is possible in native Jira/JQL (but I'm obviously happy to stand corrected if someone else has an idea).

Generally speaking, an issue's status changes are captured in an issue's issue history, which you can access from the issue view in Jira or via Jira's REST API. From these status changes, you can calculate the time that an issue spent in, or before, a particular status - however it can be quite tricky to do that by hand.

This being said, if you're open to solutions from the Atlassian Marketplace, there's a number of apps available that can help with this. I'll add more information below.

Hope this helps,

Best,

Hannes

Iryna Menzheha_SaaSJet · Accepted Answer

Hi @Peter Neuhaus ! 👋
Welcome to the Community!

You're right — native JQL is limited when it comes to comparing two issue fields like created and a status change date. Unfortunately, JQL doesn’t support arithmetic between two different date fields (like status changed TO "Agreed" minus created). So, the kind of query you're trying to run isn't possible with built-in Jira functions alone.

🔍 Workaround with Native Jira: You could use Automation to write the timestamp of the first transition to "Agreed" into a custom field, then filter based on the difference using JQL + SLA/custom field logic — but this adds complexity and requires ongoing maintenance.

📈 Alternative: Try Time in Status

If you’re open to using a Marketplace app, I’d recommend checking out Time in Status.

With Time in Status, you can:

Build a report showing how long each issue spent from Created until reaching a specific status (like "Agreed")

Apply filters to display only issues where this duration > 7 days

Export the data to Excel or CSV

Visualize bottlenecks or long waits in workflow stages

📊 In your case,also you can use  Time Metrics Tracker| Time Between Statuses app and set report:

From status: Created (or simply the issue creation date)

To status: Agreed Then filter issues where the time exceeds 7 days.

It’s a much easier way to get exactly the insights you need without complex JQL or automation.
Apps developed by my team.

Hope this helps! Let me know if you want help configuring a report like that. 🙌

Peter Neuhaus · Accepted Answer

Thank you for your answers, @Hannes Obweger - JXL for Jira, @Iryna Menzheha_SaaSJet

To summarize:

JQL has limited support for date arithmetics, and, with regard to my problem, you cannot calculate the difference between two status changes and use that difference in a comparison.

There are a few workarounds that do require some effort:

* Native Jira: create a new field for the time difference that automatically gets updated on status change
* Using apps from Atlassian Marketplace, e.g.* Time in Status
* Time Metrics Tracker| Time Between Statuses
* JXL for Jira

I will consider you suggestions.

Thanks!

Peter