Difference in JQL vs Basic searching

[David Cleveland]

Using this query to get tickets created in last month, but am seeing different numbers when using JQL vs Basic.

JQL: 

project in (###) AND issuetype in (Bug, Story) AND reporter in (###) AND created < endOfMonth(-1) and created >= startOfMonth(-1)

basic

[Bill Sheboy · Accepted solution]

Hi @David Cleveland -- Welcome to the Atlassian Community!

Did you notice your basic search is checking all projects and the View All Issues (advanced JQL) is searching for specific ones?

Did you notice your basic search is including the last day of October and your advanced JQL is using created < endOfMonth(-1) and not <= for the value?  I wonder if you have any issues on 31 October which are excluded as a result of that.

Kind regards,
Bill

[David Cleveland]

What if i had the JQL reporter in (##) AND created > startOfMonth(-1M) AND created < startOfMonth() ORDER BY created DESC

the JQL is returning more results than the basic search. Basic search would be the same as above.

[Bill Sheboy]

Something is different, either in how you are building the queries or in your issue data changing.  Please try this:

1. Create your basic search, and test it to return what you want
2. Then select "Switch to JQL" on the right-side of the page
3. Compare that generated JQL to what you were using as stand-alone JQL to observe the differences

[David Cleveland]

Looks like in the JQL it is pulling November 1st where as in the basic, it is stopping at October 31st. Any thoughts on how to change the JQL to remove the first day of the current month?

[Bill Sheboy]

What I do not know is does startOfMonth() use date/time or just date.  I suspect it is date/time, and so you could try this:

created < startOfMonth(-1m)

That should remove 1 minute, and so move into the prior day.  If that does not work, start walking back until 1 November drops off.

[David Cleveland]

When going all the way to -1000m, it made the two equal each other. thanks!

[Sanjen Bariki]

Hi @David Cleveland ,

Welcome to atlassian community!

You are correct @Bill Sheboy .

As per the shared screen, JQL and basic are too difference.

In JQL you have mentioned for a specific project and added the duration for a specific once.

In Basic you have mentioned for all the projects and it will include all the duration as you added manually. 

Regards,

Sanjen