Query cases created in certain time period for each day.

[Allen Chen]

Hi all,

I am wondering how to efficiently query cases created during certain period of time for each day for a week?

I want to query cases create 1am-9am, 9am-17pm and 17pm-1am for each day during 2020-06-08 to 2020-06-14.

project = "xxx" AND (createdDate >= "2020-06-08 09:00" AND createdDate <= "2020-06-08 17:00")

with this line, I can query cases created in certain period of time for just one day.

how to query cases created during 9am-17pm from 2020-06-08 to 2020-06-14?

Thanks.

[Andy Heinzer]

Hi Allen,

I understand that you want to be able to use JQL to find issues created within a certain time frame each day for a week.   I'm confident that JQL can help you do this, but it might be a bit complex of a query to setup.

We can use the startOfDay() function as a starting point.  This will help us not have to select the specific day in question for starters.  But also this can help because we can use time modifiers to this. 

startOfDay()

startOfDay("inc")

where inc is an optional increment of (+/-)nn(y|M|w|d|h|m). If the time unit qualifier is omitted, it defaults to the natural period of the function, e.g. startOfDay("+1") is the same as startOfDay("+1d"). If the plus/minus (+/-) sign is omitted, plus is assumed.

So while you could find all the issues created today between 9am and 5pm with a query such as

project=abc and created > startOfDay("+9h") and created < startOfDay("+17h")

We could use this same logic to find both today's and yesterday's issues created in that same time frame, such as

project=abc and ( (created > startOfDay("+9h") and created < startOfDay("+17h")) OR (created > startOfDay("-15h") and created < startOfDay("-7h")) )

This query will then be able to show both today's and yesterday's issues in that project created between those same hours.  You could expand this to the next day, and the next.  Each time though you would need to add another OR inside that set of parentheses and then subtract another 24 hours from the previous values of each (so -39h and -31h respectively).

I hope this helps.

Andy