Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,456,776
Community Members
 
Community Events
176
Community Groups

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

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!

3 answers

5 votes
Antoine Berry Community Leader Apr 03, 2019

Hi,

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

created < "2018-10-01"

?

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 Community Leader Apr 03, 2019

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

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 Community Leader Apr 03, 2019

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)

Like # people like this

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

Like # people like this

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

Like Antoine Berry likes this

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

Like Antoine Berry likes this

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 Community Leader Oct 13, 2020

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.

Like # people like this

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...

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

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

 

Cheers!

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events