Broken Equality Operators WRT Dates in JIRA 6.0.8 JQL?

One of my users asked me about why the following two queries returned different results:

created >= 2013-09-01 AND created <= 2013-09-30

and

created >= startOfMonth(-1M) AND created <= endOfMonth(-1M)

The latter query returned 3 more issues than the former. The three that were only returned by the latter query were all created on September 30th. I replaced the >= and <= operators with > and < operators and moved the dates out by a day and got the same results as the query above with the startOfMonth() and endOfMonth() functions. Curious, I tried this:

created = 2013-09-30

It returned nothing, even though I was expecting that it'd return the three issues that were created on 2013-09-30.

I'm kind of surprised that JIRA didn't replace the startOfMonth() and endOfMonth() functions with the 2013-09-01 and 2013-09-30 dates, respectively, then reevaluate the JQL query. If it did this, it'd be the same as that first query, so something different is happening.

I'm assuming that I'm misunderstanding something, but I cannot firgure out what. Ideas?

Thanks,
Pete

1 answer

1 accepted

1 votes

In JIRA, the date means the time at 12:00AM. So the first query transalates to "issues created after 09/01 12:00AM and before 09/30 12:00AM". It won't include anything created on 09/30 because they were created after 12:00AM.

That is exactly why it is not returning anything when you try "created = 2013-09-30".

endOfMonth(), I guess, returns the issues created on 09/30 as well and that is why you are seeing 3 more issues.

This explains it, though I think the default times chosen are a little non-intuitive and should depend on the comparison. If using ">=" then the default time Atlassian uses makes sense, but for ">=", the default time should be 23:59. For the "=" comparison, I really don't think there should be a default time. I'm guessing that the startOfMonth() and endOfMonth() funcations probably take that into consideration, since they do return the correct values.

Thanks for the explanation.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,798 views 11 18
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot