How to filter tickets specifically by time of day

[Mario Volic]

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.

[Haran Kumar · Accepted solution]

Hi @Mario Volic , 

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

[Haran Kumar]

Hi @Mario Volic 
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")

[Mario Volic]

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

[Andrew Laden]

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.

[Haran Kumar]

@Mario Volic  , 

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

 

[PVS · Accepted solution]

Hi @Mario Volic 

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

[Mario Volic]

Hi

Thanks for your Answer!

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

[Haran Kumar]

@Mario Volic  , 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