JQL 'from - to' clause not working: showing hidden issues when altering date range

[Andrew Blake]

I have the following JQL intended to display all issues sent back to development from the QA team within the last week:

Project = sofi AND status changed from QA to (open, "in progress", "pull requested", "code review", blocked) AFTER (startOfWeek(-1d))

I executed the query as shown, and then I tried increasing the timeframe by changing the "-1" to a "-2" in startofweek(), just to make sure everything was working properly. When I did that, the results included a greater range of dates (as expected), but also brought in more issues for the dates that were already present the first time. For example, say the first query (with "startOfWeek(-1d)") shows 3 results for the date of August 14, and more going back to August 12. When I change the query to "startOfWeek(-2d)", the results show until August 10th, but now include 5 issues for the 14th.

I verified that it is the "from - to" clause causing the problem by replicating the same situation using another query that does not include "from - to" (Project = sofi AND created > startOfWeek(-1d)). Altering the "startOfWeek(-Xd)" clause does not cause anything out of the ordinary here.

Have any of you experienced this before?

[Andrew Blake]

This is the query that works for me:

project = "SoFi Master Project" AND status CHANGED FROM "QA" TO (Open, "In Progress", "Ready for QA") AFTER startOfDay(-7d)

I didn't realize this, but the CHANGED keyword does focus on the change in <field> that you specify. See https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-operators-reference-764478341.html for more information about it.

[Andy Nguyen]

Hey Andrew,

The JQL that you use has nothing to do with Updated. Literally, it returns issues in a project that were transitioned at some point of time, regardless of the last updates made to them.

In other words, the last updated date is not necessarily the transitioned date, which I think is what you're looking for. Unfortunately, there's no Transitioned date in JIRA, so I would say there's no way to work around this using JQL. You will need to manually check the Issue History to know exactly when the issues were transitioned.

If SQL works for you, this query may help:

select p.pkey, ji.issuenum, cg.author, cg.created, ci.oldstring, ci.newstring
from project p inner join jiraissue ji on p.id = ji.project
inner join changegroup cg on ji.id = cg.issueid
inner join changeitem ci on cg.id = ci.groupid
where ci.field = 'status';

It tells you who transitioned which issue from-to which statuses on which date. You just need to add some more conditions to the WHERE clause to refine the results e.g. where pkey = 'SOFI'.

I hope this helps.

Best,

Andy

[Andrew Blake]

Andy,

Thanks for your help. I'm not aware of a way to use SQL on JIRA issues; otherwise, I would try what you suggested.

Drew

[Andrew Blake]

I think the problem was that ANY update to an issue—status alteration, comment made, assignee change, whatever—causes the "Update" field to change, which means that the data I'm trying to query is "polluted" with other updates that I don't need.

Is there a way around this?

[Andy Nguyen]

Hey Andrew,

When you say:

for the date of August 14

What date are you referring to: Created, Updated, etc.?

If Updated, then we may explain this as follows:

• The 2 issues were last transitioned from QA to one of the statuses before startOfWeek(-1d)
• They were then not transitioned any more, but last updated on Aug 14
• So if you query those that were transitioned after startOfWeek(-1d), you don't get them
• And if you query those that were transitioned after startOfWeek(-2d), you get them, and their Updated date is Aug 14

I hope this makes sense.

[Andrew Blake]

That does make sense. I am looking at the Updated date field, but it seems the problem as I explained it persists. Here are two screenshots of two new queries I made (here I use startOfDay() instead of startOfWeek(), and 0 days and -1 days instead of -1 and -2 days):

As you can see, the first query shows only three results for "Updated August 16," while the second shows twelve for the same day when the startOfDay() field is extended backwards by one day (and to clarify, the "from - to" clause is relating to the date the status was updated from QA to another status, not when the issue was first created).

[Andrew Blake]

Also, I just wanted to thank you for helping me out.