Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Searching issues with JQL based of time of day

Andris Grinbergs
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Feb 28, 2016

Hi,

 

I need a JQL search in JIRA which finds issues created during nighttime. Can someone tell me if it is possible?

something like "created after 1:00AM and created before 7:00AM and created after 1.1.2015 and created before 31.12.2015".

BR,

Andris.

4 answers

1 vote
Boris Georgiev _Appfire_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Feb 29, 2016

As far as I know you can search for issues created in a specified time interval for a specific date, but you can't search for issues by time for a date range.

For example to find the issues created between 1AM and 7AM today you can use the query below:

created >=startOfDay(1h) AND created <= startOfDay(7h)

If you want to search for the same in a date range - you may try to create a custom post-function. I'm not aware of an add-on providing such functionality but you may try searching the marketplace for such an add-on.

I was able to achieve this. Here's how I did it. 

For this example, assume you want to split tickets into those submitted between 9am and 5pm aka "business hours" and tickets submitted before 9am or after 5pm aka "non-business hours" tickets so you can measure how many tickets come during each time period on an ongoing basis.

  1. Set up an automation to label tickets at creation either as "business hours" or "non-business hours" - the way I did that was to set up logic that said: 
    1. If: created > startOfDay("+9h") and created < startOfDay("+17h") then apply label "business hours"
    2. Else if: created < startOfDay("+9h") or created > startofDay ("+17h")  then apply label "non-business hours" 
  2. Use the label in JQL later to search: 
    1. labels = "business hours"

NOTE: This solution will only work for future-looking tickets

If you wanted to apply this to historic data, I'd suggest downloading all of the data you care about, then using excel to split it into the right buckets, and then using the bulk update function to update the labels to match the above. 

Of course if you wanted to be even more specific about this, you could create a label for each hour of the day, and then be able to modify the definition of the groups more easily (for example, if you wanted to move business hours to be 8am - 4pm, it would be more difficult with the solution proposed above) 

@Andris Grinbergs, hello! Have you found an answer to this question?

Andris Grinbergs
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
Oct 22, 2018

Unfortunatelly - no, however I was able to avoid it by using Calendars of Jira Service Desk, just can't afford SLA reindex, since that will break data.

It's also possible to find such issues with excel from CSV export or from SQL database itself...

Hi @Andris Grinbergs,

Hope you are doing well.

Were you able to achieve that output ?

Suri

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events