It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to filter tickets specifically by time of day

I want to find out how many tickets are processed during opening hours (08.00-18.00) and how many are processed outside opening hours (18.00-08.00).
This so that I can show in a dashboard how the division is.

3 answers

2 accepted

0 votes
Answer accepted

Hi @M V 

 

 

Use This JQL

project = "XYZ AND issuetype = bug AND status changed to closed AND updatedDate > "2019/21/10 08:00" AND updatedDate < "2019/21/10 18:00"

 

 

This is a much simpler query to use, in my opinion.

 

https://confluence.atlassian.com/jirasoftwarecloud/advanced-searching-fields-reference-764478339.html?_ga=2.26692669.1646493853.1571636941-1556981161.1571636941#Advancedsearching-fieldsreference-ResolvedResolutionDateresolutionDateResolved

Hi

Thanks for your Answer!

What if I want all tickets during a year and not a specific day?

0 votes
Answer accepted

Hi @M V , 

created >= startOfDay("+8h") AND created <= startOfDay("+18h") 

will give you ticket from morning 8 to eve 18 hrs and similar 

created >= startOfDay("+18h") AND created <= endOfDay("+8h") 

for tickets between 18hrs and 08 hrs of next day

Regards
Haran

Hi @M V 
For all ticket in year you can use the following, 

created >= startOfYear() AND created <= endOfYear() AND created >= startOfDay("+8h") AND created <= startOfDay("+18h")


created >= startOfYear() AND created <= endOfYear() AND created >= startOfDay("+18h") AND created <= endOfDay("+8h")

Like M V likes this

Can you make an example for the year. It doesn't work.

This wont work as written. The "startOf" and "EndOf" are all relative to the current date, not to a specific date field.

This isn't possible in with just native jira tools. You will have to use one of the scripted addons. (Scriptrunner, powerscripts) to make a function that you can use in your JQL.

WIth powerscripts you can make a JQL like this.

key in silJQLExpression("(hour(created) >=17) or (hour (created) <=6)", "project = SD  and created >= 2019-01-01 and created < 2019-02-1") ORDER BY created ASC

Im sure there is a way in scriptrunner, but I dont use it so I cant speak to it.

Like M V likes this

@M V  , 

 

created >= startOfYear() AND created <= endOfYear() AND created >= startOfDay("+8h") AND created <= startOfDay("+18h")


Apologies, I was wrong, as the startofDay function return only the issues that are created today irrepective of the previous filtering that is considered. 

Im afraid that if we right the JQL code for this it will be terribly long and something like seperating each day , followed by each month and the year.

Unfortunate looping cannot be achieved in JQL .  Only way to achieve this is use any plugin or to determine the following JQL as a function 

created >= startOfDay("+8h") AND created <= startOfDay("+18h")

And using that function with the year like , 

created >= startofYear() AND Function AND created <= enofYear(). 

Currently its not possible to directly sort using JQL. 

May be you can work to get your desired output by mentioning the date in the  JQL
created >= 'yyyy-mm-dd 08:00' AND created <='yyyy-mm-dd 18:00' 

by replacing yyyy-mm-dd by all dates required.  But it is a tedious work to mentioned all dates and get the output. 

Better to use script runner to write the function or use any exisiting plugin to get the data. 

I shall try to work on this further and will let you know if any identified. 

Regards & Thanks
Haran

 

Like M V likes this

@M V  , I have been thinking and I feel the following may be useful also , 

  • Create a custom field (Single list Drop down) like opening hours & Outside opening hours. 
  • So sort out all tickets using the custom field based on the value. 
  • This if you give within StartOfYear() and EndOfYear() , you can achieve want you wanted. 

To add more accessibility , 

  1. You can save the issue search as filter.
  2. Subscribe to that filter and you will get daily mails containing all the filter tickets, accordingly. 

So by this you can have record over an year with each days ticket created in open hours & outside open hours. 

 

Regards
Haran

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

274 views 1 2
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you