JQL for time range

[Manish Sonowal]

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?

[Trudy Claspill · Accepted solution]

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]

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

[rodney e]

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]

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]

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]

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]

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