How to find issues where one date field is greater/smaller/equal than/to another date field

[#1]

Use case:

1. In project TOP you have an issue type Dev Task which has two date-picker fields, My Due Date and Completed On.

2. You want to find all issues where the completion date is greater than the due date.

JQL does not let you compare two date fields, but ScriptRunner add-on does.

As per ScriptRunner documentation, you use the dateCompare() function, like this:

project = TOP and issuetype = "Dev Task" and "Completed On" is not EMPTY and "My Due Date" is not EMPTY and issueFunction in dateCompare("", "Completed On > My Due Date)") 

This query works fine when both fields are of type date-time picker.

However, if either or both fields are of type date picker, the above query sometimes does not work correctly - when the two fields have the same date - you might get them in the query

The underlying reason is that Jira keeps all dates in Unix Epoch format which includes the time (hours/minutes/seconds).

In the case of date picker fields there is no way to tell what the time would be.

The dateCompare() function retrieves the internal values of the two fields and compares them numerically.

For example, if the internal value of Completed On is 2019-03-13 17:00:15 and value of My Due Date is 2019-03-13 17:00:05, the issue will be returned in the query even though it should not, since both fields are the same date.

The workaround proposed by ScriptRunner support is to use Groovy code in the JQL which will clear the time part of the fields. Note that in this case you cannot use the field names and you must use the custom field IDs:

project = top AND cf[18384] is not EMPTY AND cf[18388] is not EMPTY AND issueFunction in expression("", "customfield_18388.clearTime() > customfield_18384.clearTime()")

The cf[18384] and cf[18388] are the internal JIRA names of the custom fields My Due Date and Completed On, respectively.

[Marc Minten _EVS_]

Hi,

I do not understand why you would need the cf[...] construction ? Only within the "expression" function you need to refer to the customfields as "customfield_xxxx"

To overcome this last issue, I would create a new custom JQL function (with scriptrunner). Something like "completedAfterDueDate()". You do not need any arguments (or maybe just add a subquery as argument in order to improve performance ?), and even can encapsulate the "... is not empty" stuff in your function.

Your query would look like

project = TOP and issueFunction in completedAfterDueDate()

[Amir Katz (Outseer)]

Hi Marc,

Thanks for your useful insights.

Indeed, you do not need to use the cf[nnnnn] construct - can use the custom field names instead in the first part. But since the second part of the query (inside the expression() construct) must refer to the CFs by their IDs, IMO it makes more sense to be consistent in the same query.

Regarding your 2nd point of creating my own function - I don't have experience with that, but it's definitely something that I will look into down the road.

However, if I do that, my approach would be to create a general-purpose function without hard-coding the field names in it. I have similar fields, with different names (legacy names), in other projects, and I hate to create multiple functions that do the same thing.

Basically, what the world needs ((;-) is a function like you suggested, with this signature:  

completedAfterDueDate(<completed-date-field-name>, <due-date-field-name>)

[Marc Minten _EVS_]

Hi Amir,

Indeed, I was just giving a simple example of a JQL function. I agree, it would be much better to build a more general purpose function :-)

Look at the doc for building JQL functions, it is very clear (to me ;-)) and easy to use! I have created several!

Good luck

[Amir Katz (Outseer)]

A quick question to the audience - has anyone tried this in Jira Cloud?

[Slava Gefen]

@Amir Katz (Outseer) A quick question to the audience - has anyone tried this in Jira Cloud?

Hey, found this community conversation, also looking for Cloud solution:

Solved: We are trying to compare two date/timestamp fields... (atlassian.com)