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,298,259
Community Members
 
Community Events
165
Community Groups

jql query - time spent between two dates

Hi, I am wanting to create a filter using JQL to find a list of issues that have had time spent on them (logged work) during a specific time period. 

I have created this one for example but it returns no results:

project = X AND worklogDate >= startOfYear(2018) AND worklogDate <= endOfMonth(20180430)

I know that there is issues with time logged between those dates so I don't understand why the JQL query is showing as correct (no errors) however no results will return.

Can anyone provide some help with my jql query?

I cannot get any add-ons/plugins at this time so need to rely on jql for this. Just need to get it write to return results/records.

3 answers

1 vote
Ollie Guan Community Leader May 17, 2018

worklogDate >= startOfYear() and worklogDate <= startOfMonth()

this worked

project = "X" AND worklogDate >= startOfYear() AND worklogDate <= startOfMonth()

however when i compare the total time spent to tempo it differs a bit

close enough for me for now

thank you

Like Rafael Vidal likes this

I know that this post is a bit dated.

 

I'm trying to run a JQL script that pulls work logs between two dates.  I have, as a test, run the following Script from April 1st to April 1st.  I have also attached a screenshot of my results and redacted information to just show the result sets.

 

The reason I'm trying to use JQL is I have another application that can accept JQL scripts as filters in which then I can create dynamic reports.

 

I don't understand why my result set it showing a total of 26 hours to this search.

 

It appears that it is returning the TOTAL logged hours to the ticket which I'm not sure why that is the case considering I have written the query to only looking for a single date.

 

Is there anyway to do this with the out of the box JQL scripts?  We simply cannot get another plugin as a majority of plugins pricing model is based off number of users inside Jira which will make it far too expensive.

 

JQLDate.png

Andy Heinzer Atlassian Team Jun 18, 2020

Hi @Josh Tintner 

What you are trying to do here is use JQL to return just worklogs.  But JQL is only designed to return Jira issues that match the criteria.   The ask in the original post is just to find Jira issues that have had any logged time to them within a date range.  Jira's JQL can do that.

But your ask here is slightly different.  I understand you want to only return the worklogs in that time period, but the way JQL works it cannot do exactly that.  It can only return the Jira issues that match the criteria.  There is an existing feature request to be able to search by the worklogs themselves over in JRASERVER-12312 and JRACLOUD-12312.

There might be an app in marketplace that could help here, but I understand that is not desirable in your case.  Perhaps you could export these issues that match the JQL criteria to a CSV and filter these other values out with a tool like excel to be better able to see the times here.  But I understand that probably is not desirable either if you are trying to use the JQL filter in order reports/gadgets.

Sorry I don't have a perfect solution here for the problem you pose, but I hope my explanation helps to clarify how this works better.

Andy

Like # people like this

@Andy Heinzer  @Josh Tintner Do we still have a workaround for this ? 

0 votes

Can you try "worklogDate >= startOfYear() and worklogDate <= startOfMonth(-1)" ? 

i tried 

project = "X" AND worklogDate >= startOfYear(2018) AND worklogDate <= startOfMonth(-1)

and still got the message :

No issues were found to match your search

Suggest an answer

Log in or Sign up to answer
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