nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

We are trying to compare two date/timestamp fields using the JQL/ advanced search

Esther Evangeline. V

When we used the issuefunction in datecompare("", updated>created) this seems to be working in the jira server. When tried to replicate same in Jira cloud, the issuefunction is missing in the JQL. Is there a way to compare two custom Timestamp fields.

Accepted answers

Jack Brickey

JQL does not provide date compare capabilities. Maybe you could achieve your goal using automation. if this is a common need consider automation + custom field.

Nic Brough -Adaptavist-

issuefunction is a function in Scriptrunner, have you installed that?

Kristian Walker _Adaptavist_

Hi Esther,

I can confirm that ScriptRunner for Jira Cloud provides the dateCompare() JQL function as documented here that can be used for searching against dates and the documentation page linked provides examples of how to use this query in Jira Cloud.

Also it is worth noting in Jira Cloud the enhanced search queries can only be run on the enhanced search page as documented here.

I hope this information helps.

Regards,

Kristian

All comments

Jack Brickey

JQL does not provide date compare capabilities. Maybe you could achieve your goal using automation. if this is a common need consider automation + custom field.

Nic Brough -Adaptavist-

issuefunction is a function in Scriptrunner, have you installed that?

Kristian Walker _Adaptavist_

Hi Esther,

Also it is worth noting in Jira Cloud the enhanced search queries can only be run on the enhanced search page as documented here.

I hope this information helps.

Regards,

Kristian

Esther Evangeline. V

Hi Kristian,

Thanks for the understanding, Seems like we don't have the script runner in our Jira cloud and is there any way where I could some kind of date comparison logic for the JQL directly without having to add the script runner.

Regards,

Esther. V

Nic Brough -Adaptavist-

You will need an app to provide a function like that. Scriptrunner is the only one I know off the top of my head, but I've not looked for others (As an adaptavist, I tend to have SR on tap, so I haven't needed to look)

Kristian Walker _Adaptavist_

Hi Esther,

I can confirm you will need an App as Nic has said as this kind of query is not possible out of the box in Jira Cloud.

If you do not want to purchase the full ScriptRunner for Jira Cloud App then you can look at our Enhanced Search app which provides just the JQL functions.

I hope this helps.

Regards,

Kristian

Neha Dua

Yes script runner is installed in JIRA however I and Esther found that "the date compare function in the middle of the expression is not natively supported by JIRA Cloud. "

mentioned in below link.

https://scriptrunner-docs.connect.adaptavist.com/jiracloud/enhanced-search.html#_troubleshooting_tips

Still unable to understand how we can use it as subquery as alternate solution provided in above link . Can someone please assist on this.

Daria Pavlova_43pf7r

Hi, Esther!

Sorry for bothering you with the question but can you please give me an example of how issueFunction in dateCompare("", updated>created) worked? I am trying to use this but get the error:
Error in the JQL Query: Expecting ')' or ',' but got '>'. (line 1, character 67)

I mean the whole JQL line as an example so I can repeat with my data.

Thank you!

Juan Francisco Sacristan Garcia_vxli6x

issueFunction in dateCompare("", "updated>created")

Bryan_2v26ke

You will get answers faster if you DON'T ask questions an an answer. Make a new question and you will get answers that way.

Amir Katz (Outseer)

To expand on a previous suggestion, here is how to do it with automation, without ScriptRunner:

Create two new number fields, CreatedAsNum and UpdatedAsNum
Add them to the issue screen(s), and move them to a new tab, call it Read-Only Fields
1. This way users will usually not bother with them
2. Not absolutely necessary, but it's helpful (for the Jira admin) to be able to see the fields
Using automation and Smart Values, convert the value of Created from date/time to absolute time/seconds since the Epoch, and set the CreatedAsNum to this number
Do the same for the Updated

Now you can use JQL to compare the two number fields.

An alternative option is to create a Smart Value that compares the fields (using the "compareTo()" function), set it to a new number field called 'CreatedVsUpdated' and then you can use it in the JQL. See here .

With both soultion you need to decide when to trigger the automation, and that depends on your needs.

The simplest triggering would be on an update of either of those two fields.

Caveat: I have not tested the above solutions.

Nic Brough -Adaptavist-

I'm afraid this is wrong. JQL does not have any way to compare two fields.

You can indeed do it in an automation function, but that's not what the question was about.

Fabio Manzoni

Hey guys,

The suggestion to use automation to compare two dates worked for me.

My need is to compare whether the user changed the due date to a date higher than the previously set date.

I did it like this:
- my final delivery date is duedate field.
- I created a second custom date field.

- In automation using smart values with the date comparison property, see the documentation https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/ there is an if that compares both fields and if the new date set in the due date is higher, it changes a number field (counter) and each time the due date is changed within this condition it will increase 1 more.

- To populate the custom field with the same value as the due date, there is a second automation with a delay of 20 seconds. In my case this time is enough so that the fields are not the same at the time of comparison.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

jira-software

jira-software-cloud

jira-cloud

atl-gruland