JQL for time range

Manish Sonowal March 16, 2021

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
Trudy Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 16, 2021

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.

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 16, 2021

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
0 votes
rodney e March 16, 2022

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!

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 16, 2022

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

rodney e March 16, 2022

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")

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 16, 2022

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.

rodney e March 16, 2022

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
AUG Leaders

Atlassian Community Events