Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Most popular hours of ticket creation

Michal Hoffman April 24, 2018

Hello All,

Is there a JQL that will return all tickets created for instance between 10:00 and 11:00 in last 6 months? We are trying to find out which hours should have the best coverage.

Cheers!

Michal

2 answers

1 accepted

1 vote
Answer accepted
Nic Brough -Adaptavist-
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.
April 26, 2018

In theory, you could write JQL that says "for yesterday, show me issues created between 10am and 11am".  Then "for the day before yesterday, show me issues created between 10am and 11am".  Then "for two days ago, show me issues created between 10am and 11am".

But it is far too clunky and JQL queries have a character limit which you'll hit within a week, let alone 6 months.

Your database query is not going to be a lot better if you go that way.

There are, however, two much better ways to achieve this, and both have the benefit of being far more flexible than a hard-coded 10-11.  On the down-side, they both need code.

Option 1:  Write a JQL function for "created in hour of day"

Option 2:  Write a "scripted field" that looks at "created" and only shows the hour part of the date/time.

Michal Hoffman April 27, 2018

Unfortunately I don't have the access.

Thank You

0 votes
Bob Moore
Contributor
April 24, 2018

Hi michal,

If you are trying to specifically get, "all tickets created for instance between 10:00 and 11:00 in last 6 months" you could use JQL like the following:

project = "Project Name" AND created >= "2017/06/10 10:00" AND created < "2017/12/10 11:00"

Replace the Project Name with your project or you can use an in statement if you have multiple projects. Modify the dates as you need.

Michal Hoffman April 26, 2018

Thanks For your answer Steven.

That's not what I need unfortunately.

That will give me tickets opened outside of the one hour window (10:00 - 11:00).

I need a query that will combine results of queries below

project = "Project Name" AND created >= "2017/06/10 10:00" AND created < "2017/06/10 11:00"

project = "Project Name" AND created >= "2017/06/11 10:00" AND created < "2017/06/11 11:00"

project = "Project Name" AND created >= "2017/06/12 10:00" AND created < "2017/06/12 11:00"

project = "Project Name" AND created >= "2017/06/13 10:00" AND created < "2017/06/13 11:00"

Thank You

Bob Moore
Contributor
April 26, 2018

Apologies for the misunderstanding. 

 

I'm not sure you'll be able to get this in JQL. You'll have to query the database directly!

Michal Hoffman April 27, 2018

Thank You anyway

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events