Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,256,595
Community Members
 
Community Events
164
Community Groups

Running JQL Query - Updated Date not filtering

Good evening, 

I am trying to run a query for activty, when I select "Worked on" feature per person, I am trying to update the JQL query to show only those items within the last 24 hours. I used this:

assignee in (5c98dcd1*****01c20dc37) AND updated = endOfDay(-1) OR reporter in (5c98dcd1*****01c20dc37) OR creator in (5c98dcd1*****01c20dc37) ORDER BY updated DESC

**** just for protecting data, not actually in query

However, the results are not changing. Can someone help me figure out what is wrong with the JQL that I am using?

Thank you! 

 

 

4 answers

0 votes
yuliia_b__SaaSJet_ Marketplace Partner Apr 10, 2021

Hi @Kaitlin Meehan 

If you won't find a solution with JQL, you can try Dynamic Filters: JQL Replacer add-on. It's created by my team to simplify database queries. Here is an example:

1. Set data you need with the help of visual composer.

assignee.png

2. Choose the required date or time:1 day.png

3. Select the created Smart Fields to display the result:

smart.png

Hope you find the best option for you.

Regards

Hi @Kaitlin Meehan  -- Welcome to the Atlassian Community!

Please try updating your query for the updated check to use >= rather than =

... AND updated >= endOfDay(-1) ...

Also, the placement of your OR statements may not be what you are seeking.  Are you really looking for things which were reported by a specific person, regardless of update time-frame?  If not, please try this:

( assignee in (5c98dcd1*****01c20dc37)
 OR reporter in (5c98dcd1*****01c20dc37)
OR creator in (5c98dcd1*****01c20dc37) )
AND updated >= endOfDay(-1)
ORDER BY updated DESC

Best regards,

Bill

Hey Bill, 

Thank you so much! So how it has been requested to track activity for my team is tasks we not only created but also listed as a watcher. I believe reporter and creator would be the same thing, but watcher is important because if we made a comment that is not tracked by reporter or creator in activty from my understanding. 

I tried the order and with >= as well :

assignee in (5c98dcd167af62601c20dc37) OR reporter in (5c98dcd167af62601c20dc37) OR watcher in (5c98dcd167af62601c20dc37) AND updated >= endOfDay(-1) ORDER BY updated DESC

and I am still getting over 1000+ results, which I know I haven't done that many in a day haha. 

 

Thank you again for your help! 

Hi Kaitlin,

You are missing a set of parenthesis around the user checks.  Without those, JQL is assuming something like this;

assignee IN (...) OR reporter IN (...) OR

( watcher IN (...) AND updated... )

 

What you may want is, noting the bold parenthesis I added:

( assignee IN (...) OR reporter IN (...) OR watcher IN (...) AND updated... 

Evening, have you tried "updated < endOfDay()" since that should display the results for the given day?

  • Find issues due by the end of today:
    due < endOfDay()

  • Find issues due by the end of tomorrow:
    due < endOfDay("+1")

Hey Bhav! Thank you for helping out! I tried them both and it is still resulting in 1000+ tasks. Not sure if maybe it is just how we are utilizing JIRA in my company. 

2021-04-07 19_45_04-Issue navigator - JIRA.png2021-04-07 19_46_50-Issue navigator - JIRA.png

Like ldevore likes this
ldevore I'm New Here Feb 17, 2022

I am facing this same issue

ldevore I'm New Here Feb 17, 2022

My query should only be returning this from feb 16th, but it is instead returning thousands of things not from this date. Am I doing something wrong?Screen Shot 2022-02-17 at 9.11.16 AM.png

Hi @ldevore -- Welcome to the Atlassian Community!

Your query is using the OR to combine the results to the left and right, and so returning more issues than you expect.  (AND takes precedence before OR)

In general, try to build up your queries piece by piece, testing as you go...like this:

  • note the things which apply to all the issues, and test those separated by AND
  • note the things which apply to some issues, and test those separated by OR, wrapping them with parentheses
  • combine those things with an AND

For example:

project = DAR
AND "DAR Approver(s)" = yourEmailAddress
AND "Access Request Type" = "AWS Production"
AND created >= "2022-02-16 11:48"
AND created <= "2022-02-16 21:48"
AND (status = "Access Removed OR status = "Access Removed)
ORDER BY Key

I wrote that from your image, so please adjust as needed.

You may also want to try this free training from Atlassian on using JQL to learn more and answer questions about your project: https://university.atlassian.com/student/path/849533-gain-project-insights-through-jql

Kind regards,
Bill

Have you tried "updated < endOfDay ( )" since that should show those updated by the end of the current day? 

  • Find issues due by the end of today:
    due < endOfDay()

  • Find issues due by the end of tomorrow:
    due < endOfDay("+1")

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you