Query for date range between creation and status change

[Peter Neuhaus]

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.

[Iryna Menzheha_SaaSJet · Accepted solution]

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 solution]

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

[Hannes Obweger · Accepted solution]

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

[Hannes Obweger - JXL for Jira]

... and to expand on my last point:

If you're open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on: JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the time in [status], time between [creation or status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know!

[Ayça Erdem_OBSS_]

Hello @Peter Neuhaus 

Welcome to the community

You can use a JQL query like the following to filter issues based on their creation date:

created >= -7d

This will return issues that were created in the last 7 days.
If you're looking to identify when an issue transitioned to a certain status, you might want to check out the status CHANGED TO JQL functions. 

Best

Ayça

[Peter Neuhaus]

Hello @Ayça Erdem_OBSS_ 

Thanks.

I am aware of the `CHANGED TO` JQL query (see my original post).

What I need is a query that finds all tickets of type "Change Request" that needed more than 7 days from ticket creation to the first status change to status "Agreed". So I am looking for a way to calculate the date difference between creation date and the status change so that I can filter for something like `dateDifference(created, changed to "Agreed") > 7d` (pseudocode).

Peter