JQL to search for issues older than 30 days from a specific date

[Joseph Baker]

Hello all,

I'm trying to create a filter to see issues that older than 30 days old from a specific date (For example I want all issues older than 30 days from November 1st 2018). I've played around with "createdDate >= -30d" but that will give me issues older than 30 days from today.

Any way to enter a date into that query? I also have access to scriptrunner issuefunction if there's a way to do it through that.

Thanks!

[Antoine Berry]

Hi,

If you are going to use a custom date, why are you not setting it directly, i.e.

created < "2018-10-01"

?

[Joseph Baker]

I need the historical data on it, so rather than getting info on bugs with a specific createdDate, I need to get the issues 30 days old from a specific date.

[Antoine Berry]

I am not sure to understand. A filter returns a set of issues based on a request. If "created" is not satisfactory, you could use other system dates such as "updated" or "resolved", or any other date custom field.

For example, if you need to get issues that are between 1 month old and 2 month old on 1st November 2018 (using updated date), that would be : 

updated > "2018-09-01" and updated < "2018-10-01"

If you need to know how old a specific issue is on a specific date, I am afraid you are gonna need some scripting to fill a custom field.

Antoine

[Joseph Baker]

What I need is a query that gives me "On October 1st, these issues are 30 days old" or another way of putting it their created date is 30 days older than October 1st.

[Antoine Berry]

Well you cannot subtract days from a specific date. But you can from jira built-in date functions such as : 

startOfMonth(-1) // the first day of the previous month

startOfMonth(-14d) // 14 days prior to the first day of this month

So in your case, because you cannot do

created <= "2018-10-01" -30d   (but you can do created <= -30d)

what you actually need is 

created <= "2018-09-01" (since there are 30 days in September). You could as well use 

created <= startOfMonth(-7) (since september was 7 months ago)

[mecorre1]

+1 for : created <= "2018-10-01" -30d  functionality

[Paul Stasiak]

When I query updateDate >= "2021-07-17" I get 1525 results. This date is 40 days prior. I compared this with updateDate >= -40d and I get 527 results.

Why is there such a drastic difference between using a date 40 days prior and saying -40d?

Thanks

[Paul Stasiak]

Update: Using the date goes back the correct amount (apparently). Using -40d is going back 72 days not 40. Huh?

[Jason Mazmanian]

I used this JQL

project = NTER AND created >= -30d AND created <= 0d

Change to JQL and copy and paste. You can change it back to basic view to make changes without knowing the syntax. 

[Jason Mazmanian]

I updated the above post to contain a better query. Setting the start and end points will force the query to only select those items.

[LynnG]

I used created >= -300d AND created <= -5d to find forgotten tickets.

I embedded this into a longer JQL in Automation that runs daily and sends an email reminder. 

We had request that were exceeding SLAs, and this solved the problem

[Mario Nigrovic]

I'm surprised to discover that startOfMonth or startOfWeek accept a relative date, but "now" does not! That seems like kind of an oversight, doesn't it?

[Antoine Berry]

Hi,

If you want to use a relative date from "now",  you do not have to write it, i.e. 

created > -14d 

will retrieve issues created in the last 14 days.

[Mario Nigrovic]

I also found that startOfDay takes a relative argument.

Thanks, though. I'm quite a novice at this and the definitions of the fields are not always obvious...

[Frank]

Apparently this doesn't work for Confluence CQL although the fields are the same. The documentation is poor on examples. Any idea how to filter relative in CQL?

[Antony Trucios]

Also to expand this answer, if you want a very specific day this also works.

"Date field" <= -30d and "Date field" >= -31d

Cheers!