Help with JQL Condition startOfDay() and specific business hours in the Pacific Time

[Jesse Morales]

Hi,

I have been trying to work with printing out search results based on a few criterias

- project = "My Project" 
- created >=, <= startOfDay

- Time zone UTC, PST-8, 8:00, 1700

What I want to string together is filtering out a specific project, and all tickets created between the hours of 8am - 5pm PST in the last week. I tried a few conditions I thought would work but get errors either with the reserved character "+" plus sign or issues with getting Jira to search based on a specific time region. 

project = "Project" AND created >= startOfDay("PST-8") AND created >= "07:00" AND created <= startOfDay("PST-8") AND created <= "17:00" AND created >= -5d

project = "Project" AND created >= startOfDay("-8h") AND created >= "08:00" AND created <= "17:00”q

 project = YourProject AND created >= startOfDay(\"PST-8\") + \"7h\" AND created <= startOfDay(\"PST-8\") + \"17h\" AND created >= -7d

These are some of the errors I normally get 

Error in the JQL Query: The character '+' is a reserved JQL character. You must enclose it in a string or use the escape '\u002b' instead. (line 1, character 72) 

Error in the JQL Query: The character '+' is a reserved JQL character. You must enclose it in a string or use the escape '\u002b' instead. 

Error in the JQL Query: Expecting either 'OR' or 'AND' but got '+'. 

Any advice or feedback would be super helpful! 

[Mikael Sandberg · Accepted solution]

startOfDay() is using the timezone that is set on your account, so if your account timezone is PST then that is what startOfDay will use. You cannot define the timezone in the search, you would have to change your account's timezone in that case.

[Jesse Morales]

Thanks Mikael, I will contact my admin to help confirm our current timezone setting. 

Cheers

[Jesse Morales]

Hey Mikael, 

Good news was able to use this condition to help with my search

project = "Project" AND createdDate >= -30d AND created >= startOfDay("-0800") AND created <= startOfDay("+1700")

[Trudy Claspill · Accepted solution]

Hello @Jesse Morales 

Have you reviewed the documentation for the startOfDay() function? It details what is valid to use as parameters for it, and things like a timezone designation or trying to embed a calculation are not valid.

https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-functions/#Advancedsearchingfunctionsreference-startOfDaystartOfDay--

Additionally, created is a date and time field. If you want issues created in the past week during business hours you are going to have to have multiple criteria to check if the created date/time matches to the days in the past week and also to the business hours of each of those days. Take a look at this post:

https://community.atlassian.com/t5/Jira-questions/Find-all-issues-created-outside-of-normal-working-hours/qaq-p/1978132

I believe you will have to explicitly declare dates and times in the criteria, as shown in the answer thread on that post.

[Jesse Morales]

Hi Trudy, 

Yes I did review the referred doc, I may need to study more regarding our jira global settings currently set. I will confirm with my admin on the set timezone

The second article looks similar to some of the test JQL conditions I have tested, but as you mentioned I have to confirm my current Jira timezone. 

Hoping this can help get my search fixed! I will keep on studying and re-test. Thanks for the feedback :) 

Cheers

[Jesse Morales]

project = "Project" AND createdDate >= -30d AND created >= startOfDay("-0800") AND created <= startOfDay("+1700")

Was able to get this going after reviewing those references again. Thanks!