JQL for this condition

Question

how would the JQL look like for this condition?:

Generate a report of all HIGH severity JIRA tickets for a project with key X (or name X) that were created from 9 PM EST to 12 AM EST from the start of the year?

I tried something like :

Project = X AND Severity = "HIGH" AND created > "2015/01/01 21:00" and created < "2015/09/09",

but I need only those issues that are created between 9 PM and 12 AM everyday, from the beginning of the year.

Any ideas would be greatly appreciated.

m.antkowicz m.antkowicz · Accepted Answer · 2015-09-10T13:01:29

Unfortunately there seems to be no tool to get the hour from created date but you can workaround it.

My two ideas are:

find these tickets directly on Jira database (if you have access) it should be very easy since there are functions like hour (mySQL) or truncate (postgres)
prepare JQL filter using some generator script - it is definetely less comfortable but possible to achieve even when you have not acces to database. The worst thing is that Jira filter fields accepts only 2000 characters string so you would need to copy that filter few lines by few lines.

Little crazy but ok - it works so what's the idea? The idea is to use startOfYear() JQL function and its *offset version**. For example:
```
created >= startOfYear(21h) and created < startOfYear(24h)
```
will give you all tickets from 1 Jan 21:00 - 2 Jan 00:00

then you can use this Python script:
```
step = 27
maxDay = 1

while maxDay <= 365 + step:
    maxDay += step

    output = "project = X and Severity = HIGH and ("

    for i in range(maxDay-step, maxDay):
        output += " (created >= startOfYear(" + str( ( (i-1) * 24 ) + 21) + "h) and created < startOfYear(" + str(i*24) + "h)) or"

    output = output[:-3]
    output += ")"

    print output
    print
```
which will generate you set of JQL requests to copy-paste and execute (it is actually 15 of them - you can see here). Every set bounds 28 days because of 2000 limit of filter input in Jira.

JQL for this condition

2 Answers