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.

[isoftzis]

I have used the commentedby and commentedon in combination and found out that it does not return the issues I would expect.

JQL: 

commentedOn = '2024-03-26' AND commentedBy = currentUser()

This returns issues with comments on that date and those issues also have a comment from me.

This doesn't mean however that my comment is on that specific date!

So please be careful in using this combination because the result is different from what you might expect due to the fact that JQL's atomic data object is the issue, not the comment.

[Trudy Claspill]

Good point, @isoftzis !

[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.

[Trudy Claspill]

Here are other third party apps that extend JQL to enabled searching for issues based on date commented:

JQL Search Extensions for Jira 

JQL Tricks Plugin 

I have not used either of these myself.