how to write a JQL query on CF value,status and number of days in that status?

[Ravichandra_Itagi]

I want to write a JQL query on the basis of custom field value,status of the issue and number of days the issue in the status.

I want to add a subscription to this query. when the ticket is almost more than 2 weeks in URS -Signoff status for custom field value project, notification should go to the subscribers.  

currently am using below JQL query,

issuetype = Epic AND "Development Type" = Project AND status = "urs sign-off" AND status changed before -2w.

please suggest me whether above query is correct if it is wrong suggest me the correct one.

[Ravichandra_Itagi]

Dear Iago Docando,

yes , I am an Jira admin. I have done what you wanted me to do.  just now only I moved 3 tickets to urs sign-off status, all the 3 are showing urs sign off date as march first even when I select differen date with the help of post function which is good. URS sign off date is a custom field which I have added in USR sign off transition screen.

but when I use JQL  

issuetype = Epic AND "Development Type" = Project AND status = "urs sign-off" AND customField_XXXXX < endOfDay(-2w) 

unable to find any issues. but when I use

issuetype = Epic AND "Development Type" = Project AND status = "urs sign-off" AND customField_XXXXX < endOfDay(2w)

am able to see all the 3 issues which I have just moved to URS sign off status.

so now please suggest me , my requirement is when we move ticket to urs sign off on 1st march after 10 days means on 11th march notification should go to subscribers saying that this issue has been in urs sign off status from last 10days.

so please help me out on writing a accurate JQL query.

[Iago Docando]

There's no error. The JQL shouldnt return anything today because the transition date is aso today. Those 3 issues you mention should be returned only after 2 weeks have passed.

You could use a JQL like endOfDay(-5m) or something like that to make some testing today but based on what you say I assume it is working correctly.

Please confirm that it is working by accepting the answer.

[Ravichandra_Itagi]

If I use -5m or -20m am not getting any issues but if i use 5m its showing me all 3 issues so please clear me on -5m and 5m.

please clear me which one is exact match for my requirement.

[Iago Docando]

If today is march 1st 2019:

• endOfDay() is 2019-03-01 23:59:59 
• endOfDay(-2w) is 2019-02-15 23:59:59
• endOfDay(2w) is 2019-03-15 23:59:59
• endOfDay(-20m) is 2019-03-01 23:39:59 

The JQL is right the way I've told you. Maybe the other fields values included in your search don't match those of the issues you want to get returned?

See in the following captures how in the second example one less issue is returned because it falls outside the considered range.

[Ravichandra_Itagi]

okay endofday(-2w) means issue went to urs signoff before 15 days right? 

[Iago Docando]

2w = 2 weeks. Not exactly the same as 15 days, wich would be 15d, but you get the idea. You can use the Jira notation for time intervals.

https://confluence.atlassian.com/jira062/advanced-searching-functions-588581764.html#AdvancedSearchingFunctions-endOfDay()

I'm pretty confident about the endOfDay() part of the JQL. If you're not getting the results you should please check the rest of fields involved.

[Ravichandra_Itagi]

Hi Iago Docando,

Thanks for the response.

I am not clear about the solution you provided, can you elaborate it. 

[Iago Docando]

Sure. 

With the solution I propose, if you transition an issue today to your status "urs sign-off" the new custom field will store today's date and will never change. This field doesn't even need to be shown to the user in any screen.

The JQL i wrote for you compares that date stored in the custom field and todays date. The search will return any issue that transitioned to "urs sign-off" status more than 2 weeks ago (assuming it also matches the rests of requisites). This is taking the end of the current day as reference so maybe you want yo use endOfDay(-2w 1d)

Are you a Jira admin? Otherwise you'll have to ask your jira admin to create the custom field and edit the workflow to implement the solution.

[Iago Docando]

I don't know your workflow but the "status changed before -2w" will trigger with any status change, obviously, so maybe this isn't what you want.

I'll advise you to create a date custom field. Let's call it "date_of_the_transition_you_are interested_in".

You'll need then to add a postfunction to the transition you want to monitor. The trantition would be "update custom field" and you'll use the macro %%CURRENT_DATETIME%% as the value for the field.

Now you can create a JQL search based on thet field:

issuetype = Epic AND "Development Type" = Project AND status = "urs sign-off" AND customField_XXXXX < endOfDay(-2w)

With this I believe your need is covered. Hope it helps.