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,256,585
Community Members
 
Community Events
164
Community Groups

JQL for time range

For the past 1 year I need to find the number of issues created in different time range say between 2 am to 3 am, 3 am to 4 am and so on....

So the ask here is to find issue created based on time range.

What will be the JQL for that?

2 answers

1 accepted

2 votes
Answer accepted

Hello @Manish Sonowal 

Welcome to the community.

I don't believe there is a JQL that can be used in the Free JIRA Cloud version to get you this information.

I don't work with the Free version, so I'm not sure about what you can do to augment it with regarding to adding apps or customizations. One customization you could investigate is adding a custom field to track the hour of the day when an issue was created, and seeing if you can set that field for each issue using the Automation feature to evaluate the Created date/timestamp.

Hi @Manish Sonowal  -- Welcome to the Atlassian Community!

Agreeing with @Trudy Claspill there is no built-in way to do this with JQL...And if you just need this one time, I suggest exporting the data to a spreadsheet to report this by parsing the time out of the Created field.

Best regards,

Bill

Like Manish Sonowal likes this

Hi,

Just wanted to ask if there is any update to this since last post (3/16/2021).

I too would like to know the JQL syntax for querying for issues created in a specific time range every day.

 

- Find issues created daily between 8a - 12p and assignee to person A.

- Find issues created daily between 12p - 5p and assignee to person B.

Possible in Jira Cloud?

Thank you!

Hi @rodney e 

That is not possible for all days, but it is possible for today...and if you save the filter and subscribe to a schedule you will get a daily email with the list.

For your first query, it would be:

project = myProjectName AND created >= startOfDay("+8h") AND created <= startOfDay("+12h") AND assignee = person-A

Please look here for more information about advanced JQL: 

https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-functions/

Kind regards,
Bill

Hi @Bill Sheboy ,

 

Thank you for your response.... After failing several times, I think I found a crude work around...

 

Rule 1: project = ABC AND (created >= "2022/03/16 08:00" AND created <= "9999/03/16 11:59")

Rule 2: project = ABC AND (created >= "2022/03/16 012:00" AND created <= "9999/03/16 17:00")

Rodney, have you checked that does what you need?

That query would include anything created after March 16, 2022 at 8am, through Noon on March 16, 9999...and all the hours between

It would not return for the time range for each day in that date range.

To perform such a query for multiple days you would need a marketplace addon for JQL, to export the issues for parsing in a spreadsheet, or to use an automation rule.

Good catch @Bill Sheboy .

I think this one might work!

 

RULE 1:

project = ABC AND (created >= startofDay() AND created <= endOfDay("-721m"))

 

RULE 2: 

project = ABC AND (created >= startofDay("+720m") AND created <= endOfDay())

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
TAGS
Community showcase
Published in Jira Software

Upcoming changes to epic fields in company-managed projects

👋 Hi there Jira Community! A few months ago we shared with you plans around renaming epics in your company-managed projects. As part of these changes, we highlighted upcoming changes to epics on...

1,468 views 11 22
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you