Broken Equality Operators WRT Dates in JIRA 6.0.8 JQL?

[PeteToscano]

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

[Jobin Kuruvilla [Adaptavist] · Accepted solution]

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.

[PeteToscano]

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.