Most popular hours of ticket creation

[Michal Hoffman]

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

[Nic Brough -Adaptavist- · Accepted solution]

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]

Unfortunately I don't have the access.

Thank You

[Bob Moore]

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]

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]

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]

Thank You anyway