Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

3 comments

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()

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>)
Like Marc Minten _EVS_ likes this

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

Like Amir Katz likes this

Comment

Log in or Sign up to comment
TAGS
Community showcase
Posted in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

4,211 views 12 5
Join discussion

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