nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Nic Brough -Adaptavist-

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.

All comments

Steven Meadows (e-Core)

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

Steven Meadows (e-Core)

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!

Nic Brough -Adaptavist-

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

Michal Hoffman

Thank You anyway

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira