Help creating query using CommentedOn for tickets in time frame

[Jacob Pines]

Hi,

I am trying to create a JQL query, that will bring back only tickets which were updated with a Comment during specific times.

So to test this I created a ticket and added a comment.
I then went to Advanced Search to create the JQL query - I am limiting it to tickets where Summary contains "test" (so it works only on my test tickets), commentedBy specific user (me in this case), and CommentedOn between start of day to 9am of the same day (12am-9am).

This is the first query:
summary ~ test AND commentedBy = ba5d1234-1234-1234-1234-afd1d0e61234 and (commentedOn >= startOfDay() AND commentedOn < startOfDay(9h))

Since my comments were after 9am the new ticket I commented on should not have been in the result, but it was.

The next test was like this -
summary ~ test AND commentedBy = ba5d1234-1234-1234-1234-afd1d0e61234 and (commentedOn >= '2023-01-25' AND commentedOn < '2023-01-25 09:00')

Basically the same, except I use specific datetime value, and I get same result.

All the comments I added to the ticket were done after 9am, so should not return in query.

Any ideas what exactly I am missing?

Let me know if you need additional info.

[Trudy Claspill]

Hello @Jacob Pines 

commentedOn is not a native field that is provided for use in JQL. That is provided by a third party app that extends the JQL syntax and capabilities.

It could be that the vendor of that app did not provide support for using the startOfDay function with their commentedOn keyword.

Do you know which third party app was installed to provide the commentedOn keyword? You may have to ask your Jira Administrators to get the answer.

[Jacob Pines]

Thanks. I will check.

But in the meantime, can you or anyone suggest a query I can use to get the information I want, without using a third party app?

[Trudy Claspill]

There is no option for querying based on the date of Comments without using a third party app. If you are not getting an error message when you use "commentedOn" in your filter, then you already have a third party app.

If you look under the Apps menu, do you see entries for ScriptRunner? If so, that is the third part app that supplied the "commentedOn" and "commentedBy" keywords.

I have that app myself and did some testing.

I commented on an issue today, and did a search for

commentedOn>startOfDay()

No results were returned.

I then did a search for 

commentedOn > startOfDay(-1d)

and that returned the issue on which I just commented.

I then created a new issue and did a search for

created > startOfDay()

and that returned the issue I just created.

This suggests to me that there is some issue with the use of startOfDay specifically with the commentedOn keyword. I have not yet been able to discern the details of the difference.

[Trudy Claspill]

It might have something to do with time zone settings.

On my system the Global setting for Default User Time Zone in New York (GMT -5).

My profile time zone setting is Los Angeles (GMT -8)

I made the comment at 7:27 am (GMT -8)

If I query for

commentedOn > startOfDay(-4h)

I get the commented issue, but if I query for

commentedOn > startOfDay(-3h)

I don't get the commented issue.

[Trudy Claspill]

Hello @Jacob Pines 

I really think the issue is with the third party app that has provided the commentedOn keyword. You need to figure out which app that is, and contact their support team directly about this.

[Jacob Pines]

Thanks for the feedback. I appreciate it :)

If indeed the CommentedOn keyword is provided by ScriptRunner, then I need to find a different solution to get what I need.

I checked and was told this addon will be removed soon from our Project :(

So thanks again for the replies.