Find all issues created outside of normal working hours

[IT]

Good Day,

Trying to find a way to list all issues created outside of normal working hours (8am to 4pm) for a given period (e.g. previous quarter)

Is there some way to do this in JQL?

[Bill Sheboy · Accepted solution]

Hi @IT 

If you want to run this query for a single, specific day, the built in advanced JQL functions would help you create a saved filter...and you could then schedule that to notify you.

If instead you want to run this for all existing and future issues, that is not possible with out-of-the-box JQL features.  You would need to:

• need this often: purchase a marketplace addon for JQL to enhance search features
• need this infrequently: export the issues to a spreadsheet for analysis
• have an on-going need for this, and do not want to purchase an addon: using a custom field and automation rule, split out the time from the data/time for created to allow searches with built-in JQL features

Kind regards,
Bill

[IT]

Thanks!

The exporting to spreadsheet worked well for the analysis, though I did also find a cumbersome way to do it using JQL.

[Craig Nodwell]

I would imagine you could test on something using the startOfDay() endOfDay() combo.
Re: project = xyz and updated < startOfDay("+8h") and updated > startOfDay("+16h")
I don't have access right now but I would think something along those lines should give you what you are looking for.

Hope that gets you started :)

[Craig Nodwell]

For your review.

Advanced Searching In Jira 

[IT]

Thanks for the tip,

No joy unfortunately, tried using this -

project = ITSM AND created < startOfDay("+8h") AND created > startOfDay("+16h")

and this - 

project = ITSM AND updated < startOfDay("+8h") AND updated > startOfDay("+16h")

but for the time range where I *know* cases were created after 4pm (thanks to export to Excel) I still get 0 results 

Perhaps is possible to do substring search of the date time field and get time only?

[Craig Nodwell]

Did you try trimming this out a bit.
Re: currently it has to satisfy both AND statements, re: for the one you know that happened at 4pm.  And as mentioned above by Bill this is going to measure todays issues since it is looking at startOfDay() meaning today of course.

project = ITSM AND updated < startOfDay("+8h")

project = ITSM AND updated > startOfDay("+16h")

project = ITSM AND ( updated < startOfDay("+8h") OR updated > startOfDay("+16h"))

also again I don't have access right now but try switching your keyword around with created it works with the date functions as well.

project = ITSM AND ( created < startOfDay("+8h") OR created > startOfDay("+16h")) OR ( updated < startOfDay("+8h") OR updated > startOfDay("+16h"))

[Craig Nodwell]

If you wanted to find the ones that were created yesterday simply sub the startOfDay() out with the hard coded date params.
From the page:

• Find all issues created on 12th December 2010 before 2:00pm:
created >  "2010/12/12" and created <  "2010/12/12 14:00" 

[IT]

Thanks!

I was able to get this working using the query below, I use find and replace to expand the date ranges as needed -

project = ITSM
AND (createdDate >= "2022-03-01 16:00" AND createdDate <= "2022-03-01 23:59")
OR (createdDate >= "2022-03-02 16:00" AND createdDate <= "2022-03-02 23:59")
OR (createdDate >= "2022-03-03 16:00" AND createdDate <= "2022-03-03 23:59")
OR (createdDate >= "2022-03-04 16:00" AND createdDate <= "2022-03-04 23:59")
OR (createdDate >= "2022-03-05 16:00" AND createdDate <= "2022-03-05 23:59")
OR (createdDate >= "2022-03-06 16:00" AND createdDate <= "2022-03-06 23:59")
OR (createdDate >= "2022-03-07 16:00" AND createdDate <= "2022-03-07 23:59")
OR (createdDate >= "2022-03-08 16:00" AND createdDate <= "2022-03-08 23:59")
OR (createdDate >= "2022-03-09 16:00" AND createdDate <= "2022-03-09 23:59")
OR (createdDate >= "2022-03-10 16:00" AND createdDate <= "2022-03-10 23:59")
OR (createdDate >= "2022-03-11 16:00" AND createdDate <= "2022-03-11 23:59")
OR (createdDate >= "2022-03-12 16:00" AND createdDate <= "2022-03-12 23:59")
OR (createdDate >= "2022-03-13 16:00" AND createdDate <= "2022-03-13 23:59")
OR (createdDate >= "2022-03-14 16:00" AND createdDate <= "2022-03-14 23:59")
OR (createdDate >= "2022-03-15 16:00" AND createdDate <= "2022-03-15 23:59")
OR (createdDate >= "2022-03-16 16:00" AND createdDate <= "2022-03-16 23:59")
OR (createdDate >= "2022-03-17 16:00" AND createdDate <= "2022-03-17 23:59")
OR (createdDate >= "2022-03-18 16:00" AND createdDate <= "2022-03-18 23:59")
OR (createdDate >= "2022-03-19 16:00" AND createdDate <= "2022-03-19 23:59")
OR (createdDate >= "2022-03-20 16:00" AND createdDate <= "2022-03-20 23:59")
OR (createdDate >= "2022-03-21 16:00" AND createdDate <= "2022-03-21 23:59")
OR (createdDate >= "2022-03-22 16:00" AND createdDate <= "2022-03-22 23:59")
OR (createdDate >= "2022-03-23 16:00" AND createdDate <= "2022-03-23 23:59")
OR (createdDate >= "2022-03-24 16:00" AND createdDate <= "2022-03-24 23:59")
OR (createdDate >= "2022-03-25 16:00" AND createdDate <= "2022-03-25 23:59")
OR (createdDate >= "2022-03-26 16:00" AND createdDate <= "2022-03-26 23:59")
OR (createdDate >= "2022-03-27 16:00" AND createdDate <= "2022-03-27 23:59")
OR (createdDate >= "2022-03-28 16:00" AND createdDate <= "2022-03-28 23:59")
OR (createdDate >= "2022-03-29 16:00" AND createdDate <= "2022-03-29 23:59")
OR (createdDate >= "2022-03-30 16:00" AND createdDate <= "2022-03-30 23:59")
OR (createdDate >= "2022-03-31 16:00" AND createdDate <= "2022-03-31 23:59")