How can I use the release date of a version in JQL?

[Eitan]

Hey friends,

I'm trying to construct a query that will provide a list of issues that were created for a certain version up to three months after it has been released.

I accessed the relevant project, and under 'Versions' I found the list of released and unreleased versions, and saw that they had a column of 'Release date'. Great!

I went over to the advanced search and have tried to use it, and couldn't for the life of me find a way to reach that field.

Essentially, this is what I'm building:

issuetype = Bug AND project = <project> AND affectedVersion = <product version> AND "Bug source[Dropdown]" = Customer AND createdDate <=  ??

Just to make it clear, if I remove the createdDate part, I get all of the bugs that were created for this version. 

Ideally, I'd like to add something like "createdDate <= releaseDate(<version> + 3M)"

Any ideas?

[Patrick S · Accepted solution]

@Eitan

@Cyrille Martin's suggestion is on the right track. With the ScriptRunner plugin you can use the releaseDate() function. (See the bottom for a final query where we use affectedVersion rather than fixVersion).

This example will return issues associated with any fixVersion whose release date has occurred within the last 90 days.

project = myProject AND fixVersion in releaseDate("after -90d before 1d")

You can then tweak that to add other parameters, like if the issue was created after the releaseDate(), or you can get fancy and look for items in any release or unReleased version like the following.

fixVersion in releasedVersions() 
...
fixVersion not in releasedVersions()
...
fixVersion in unreleasedVersions()
...
fixVersion not in unreleasedVersions()

So, for a finalized query that shows bugs created within the first 3 months after a version is released, you could put the concepts above together to do the following.

project = myProject AND affectedVersion in releaseDate("after -90d") and created >= -90d

[Cyrille Martin]

hi @Eitan 

I don't see any native solution.

One way to cover your need would be to implement this JQL function with ScriptRunner : https://scriptrunner.adaptavist.com/latest/jira/custom-jql-functions.html

Regards,

Cyrille

[Jack Brickey]

Unfortunately the version release date is not a field that can be used within a JQL. The reason for this is the release date is not stored for every issue. So if you want to make your current JQL work you would have to use a static release date. The other option would be to create a custom date field and every time you make a release populate the actual date into that field. Then you could use your custom release date. Of course the downside of this is that either you would have to go back and populate the release date for all the storage issues or just live with this working going forward.

[Eitan]

Fair enough.

Then if I go ahead and manually populate the release date in the query, is there a function that I can use to get only three months after that date?

To clarify, let's assume that the version release date was November 1st, 2020.

Can I structure something like the below?

createdDate <=  "2020-11-01" + 3M

[Jack Brickey]

Why not simply…

createddated <= 2021-02-01 and createddate >= 2020-11-01

[Eitan]

Honestly, because I'm going to repeat this for a large number of versions and the less I manually type the dates, the sooner I finish this day.

Alright, I'm going to give it a go. Thanks!