JQL (status changed TO "Done" AFTER "2021/08/01") is not working

[Hiroshi Nishio]

As title says, I can not get any results by using JQL like 

• status changed TO "Done" AFTER "2021/08/01"
• status changed TO "Done" AFTER "2021-08-01"
• status changed TO Done AFTER "2021/08/01"
• status CHANGED TO "Done" AFTER "2021/08/01"

All JQL does not work. But I have surely these tickets whose status changed to done after 1st August.

[Hiroshi Nishio · Accepted solution]

Not a very direct solution, but I solved it by using status id instead of status name. For example, "Done" was "10006" for me.

It looks like the problem is that the names don't match, but it looks like they do, so I'm out of luck.

The important thing is that it doesn't matter if it is a name or an id, I hope this is the same for all users, and Done is 10006.

[Hiroshi Nishio]

Status name is "Done" as a default, whose status id is 10006 for all jira users??

[Matthias Gaiser _K15t_]

Thank you for sharing your solution @Hiroshi Nishio

In case you didn't know, you can access the list of all status in a JSON format which contains also the status id, e.g.:

https://ecosystem.atlassian.net/rest/api/2/status

[Hiroshi Nishio]

@Matthias Gaiser _K15t_ I did not know that, very useful.
By the way, I noticed this, but if I wanted to get status = Done in the so-called user sense, perhaps it would be more generic to use status category = Done and status category ID = 3 as the condition?

[Matthias Gaiser _K15t_]

It depends what you're trying to express. As you might know when you create a status you can assign it to a statusCategory, e.g. Done. So if you search for a statusCategory, this might contain multiple statuses, e.g. Resolved, Closed, Done and not only the Done state. So that's up to you what you want to filter for.

[Hiroshi Nishio]

@Matthias Gaiser _K15t_ 

Exactly, I would like to know "Done" as meaning, not "Done" as a string, so maybe status = Resolved and Closed should be treated equally as Done. I am developing an aggregate dashboard (https://workstats.dev/) like the one attached, so I am not the only one who will be using it, but an unspecified number of users.

When I think about it, I feel that perhaps I should make Status Category ID = 3 a condition. Can the status category ID be Null or Undefined?

[Matthias Gaiser _K15t_]

That sounds like a good idea. I also think you should go for status category then.

AFAIK, the status category always needs to be filled for a status.

[Matthias Gaiser _K15t_]

btw: you might want to redact your email address from the screenshot you've shared.

[Hiroshi Nishio]

Thank you very kindly!
I will follow your advice.

[Hiroshi Nishio]

@Matthias Gaiser _K15t_ 

Unfortunately, the error occurred and says "History searches do not support the 'statusCategory' field."

[Matthias Gaiser _K15t_]

Ah, I didn't think about that.

But thinking about your use case again. Why don't you filter for the resolutiondate field? When an issue is done in Jira, it should usually get a resolution which will set its resolutiondate field, so you could write a JQL like:

resolutiondate > "2022-09-07" 

[Hiroshi Nishio]

Thanks for the advice.

Just, please look at lines 2 and 3. By default, it looks like the resolutiondate is still "unresolved".

The first line is resolved, but this is resolved because it was intentionally set to be resolved when the status = Done in the Automation settings. However, there is also the problem that even if the status is changed from Done to Doing, it remains Resolved.

[Matthias Gaiser _K15t_]

Yeah, I know. This can happen.

Ideally your workflow is configured that way that when an issue is transitioned to an end state, the resolution is set (via a screen or a post-function) and when you move it out of an end state you would clear it again via a post-function.

But this is not the case for all project setups.

[Hiroshi Nishio]

I would like to know the specifications to begin with, is there anyone here from Atlassian?

[Matthias Gaiser _K15t_]

Hi @Hiroshi Nishio

I've verified your JQL statements on my instance and they work properly.

status changed TO "Done" AFTER "2017-11-28"

You can double-check the history on your issue again - other than that, I'm not sure what the problem is. On Server/DC, I'd say perform a reindex, but that's not available on Cloud.

Cheers,
Matthias.

[Hiroshi Nishio]

Hi @Matthias Gaiser _K15t_ 

What a surprise. So it doesn't seem to be a problem with the JQL itself. Hmmm, I'll check my ticket.

[Hiroshi Nishio]

@Matthias Gaiser _K15t_ 

Doublechecked my ticket and still, it seems I changed this ticket status from Doing to Done 4 hours ago, which is Sep 5, 2022. So it means my JQL should include at least this ticket but does not. 

[Matthias Gaiser _K15t_]

I agree, @Hiroshi Nishio. But I'm afraid, I can give you any other tips. I'll ping some others for advice.

[Manon Soubies-Camy]

Hi @Hiroshi Nishio,

This JQL also works on my side. Can you double-check the "Done" status? Sometimes there is a space hidden somewhere (like " Done" or "Done "). You can use the basic search to select the status you need and then switch to advanced search.

Other than that, you can also try similar JQL queries like:

status changed to Done during (2021-08-01,2022-09-05)

Hope this helps,

- Manon

[Hiroshi Nishio]

Hi @Manon Soubies-Camy 

I know what you mean, and double-checked it, but "Done" was actually "Done" because it was created by Atlassian as a default instead of me.

I will try the similar JQL you gave me.

[Hiroshi Nishio]

@Manon Soubies-Camy @Matthias Gaiser _K15t_ 

Unfortunately... it did not work.

If you do not mind, would I share my project with you??

The reason I want to do it in JQL is that I am in the process of implementing the Jira REST API v3 with OAuth2.0 (3LO) and I need the right JQL. I am experimenting with the web UI for this purpose.

[Hiroshi Nishio]

Still not working.