lookup action & {{#lookup}} returning different results

[Rachel Ang]

Hi everyone,

I am trying to send reports based on different time zones. For example:

Report 1:

Gives list of tickets created during 9-5pm Singapore time.

Report 2:

Gives list of tickets created during 9-5pm London time.

When i tried doing it using jira automation lookup actions i am able to receive the correct list when i tried to validate my query. 

This is my code under the lookup action:

Project ="ProjectName" AND created >= startOfDay() AND created <= endOfDay()

This code return me the correct tickets created for the day 

However, when i tried to print out the result by using "send message to ms teams" action with the following keyed:

{{#lookupIssues}}
{{key}} 
{{/}}

The message returned more tickets than it was returned on the previous actions ( lookup issued) , it was returning me tickets that was created 11ish Pm the previous day. 

I tried to figure out how the timing works. Apparently, it is not even the UTC timezone... 

Does anyone has any idea how can i achieve reports for different timezone?

Thank you

[Bill Sheboy]

Hi @Rachel Ang -- Welcome to the Atlassian Community!

When using JQL to check the created field, documentation indicates:

Search for issues that were created on, before, or after a particular date (or date range). Note that if a time-component is not specified, midnight will be assumed. Please note that the search results will be relative to your configured time zone (which is by default the Jira server's time zone).

https://support.atlassian.com/jira-software-cloud/docs/jql-fields/#Created

There is a suggestion to add the ability to account for time zones in JQL, which you may vote for / watch to see progress: https://jira.atlassian.com/browse/JRACLOUD-74279

The work-around noted with the suggestion is likely the same for automation rules:

• evaluate the time difference to the desired time zone
• use that difference as an offset in the JQL for the functions, such as startOfDay()
• with the revised JQL, the lookup should return what is needed

Also please note: when creating a rule and testing the JQL in the rule editor, that JQL is run with the profile and permissions of the person editing the rule.  When the rule actually runs, it uses the rule actor's information.  Please test to ensure the rule works as you expect, given this information.

Kind regards,
Bill

[Rachel Ang]

Hi Bill, 

Thank you so much for the insightful information!

However, i am currently facing this issue whereby when i try to list out the issues using {{#lookup}} under the "Send message" action, it is returning me issues created even before midnight (00:00) of the configured time zone in my account.

For example scenario:

Account timezone: Asia +08 GMT

Lookup issues return: Returns ticket created after 00:00 the same day. 

{{#lookup}} returns : Ticket created 11:00pm the day before.

Hence, when i tried to do created >= startOfDay(-5) AND created <= endOfDay(-5), it may not return the expected list 

Best regards,
Rachel

[Bill Sheboy]

That is curious...I recall similar posts when using the endOfDay() function that < should be used rather than <= for bounding within specific days:

project = myProjectName AND created >= startOfDay(-5) AND created < endOfDay(-5)

Perhaps try that first, with stand-alone queries in View All Issues (advanced search) to experiment and observe the impact.

If that does not help, I recommend working with your site admin to submit a support ticket to Atlassian to take a look: https://support.atlassian.com/contact/#/

When you hear back from them, please post what you learn to benefit the community.  Thanks!