Compare a timestamp and format 'YYYY/MM/DD', 'YYYY-MM-DD'

[Adrian Speich]

I am attempting to compare two dates in JQL and realised I am comparing a date from the calendar object and a timestamp, resulting in the below error:

Date value 'Started: Approval[Time stamp]' for field 'Deployment SLA' is invalid. Valid formats include: 'YYYY/MM/DD', 'YYYY-MM-DD', or a period format e.g. '-5d', '4w 2d'.

Is it possible to convert a timestamp to format 'YYYY/MM/DD' or 'YYYY-MM-DD'?

[Trudy Claspill · Accepted solution]

Hello @Adrian Speich 

Welcome to the Atlassian community.

Can you share with us the actual JQL you are attempting to use?

I suspect you are trying something like this:

'Deployment SLA' < 'Started: Approval[Time stamp]'

Comparing one date (or date/time) field directly to another in a JQL statement is not supported by native Jira JQL capabilities. To achieve that would require a third party app, such as ScriptRunner which provides a dateCompare() function.

(I do not work for Adaptavist and get no perks from recommending their product. It is just the one with which I am most familiar.)

There are other apps that extend JQL capabilities available in the Atlassian Marketplace , and one or more of them may also provide a comparable function.

[Adrian Speich]

Hi Trudy, 

That is unfortunate, I will need to make a request my company's admin to install ScriptRunner and this is unlikely to be approved. 

Yes, your suspicion is 100% correct regarding the JQL line in question, that is exactly what I am trying.  

Thanks

Adrian

[Trudy Claspill]

Hello @Adrian Speich 

What do you need to do with the output of the filter? What is the actually problem you are trying to solve with this filter?

Depending on what problem you are trying to solve, there may be work arounds available that don't involve buying an app.

[Adrian Speich]

The output of the filter will be used to feed a dashboard and/or exported to Excel.

I am looking to highlight issues that have exceeded their SLA date. When the ticket transitions to a particular status, 'Started: Approval [Timestamp]' is populated with now().

I want to identify any issues where 'Started: Approval [Timestamp]' exceeded the SLA ('Deployment SLA').

I export the raw data into a spreadsheet and perform the calculation via a macro at the moment.

[Trudy Claspill]

You could consider creating a custom field to annotate that has occurred, use an automation rule to populate that field, and then base your filter on that field.

Do you think that is a solution that would work?

[Adrian Speich]

What I have done is create a custom field (Started: Approval [Timestamp]) and an automation to populate it, which works as expected. 

The problem is that for my filter to work I need to compare this custom field value to another field, which does not seem to be possible due to the different formats between the fields. 

[Trudy Claspill]

It would be possible to do the comparison through an Automation rule, but not within a JQL statement.

You would need to select the issues you want to compare based on a different JQL statement such as

"Started: Approval [Timestamp]" is not empty and "Deployment SLA" is not empty and "new custom field" is empty

Once you have the issues selected in that manner, through Automation Rules you could use a Smart Value Condition step to compare the two values. You don't even have to add formatting.

Based on the results of that comparison you could set the "new custom field". 

And then you could create a filter based on the "new custom field" that could be used in your dashboards.

[Adrian Speich]

You are right. This is the correct approach. Thank you, Trudy. 

[Trudy Claspill]

You're welcome.

If my responses have helped you solve your requirement, please consider clicking on the Accept Answer button to mark your Question as Solved. This helps others searching the community find posts with validated solutions.

[Kristján Geir Mathiesen]

Hi @Adrian Speich  and welcome to the party!

Have you tried:  When querying in JQL, use the [field] ~ "jiraDate" format to extract only the date from a date/time field.

Like --> JQL query to find issues created on "2023-12-25": created ~ "jiraDate" AND createdDate = "2023-12-25"

HTH,
KGM

[Adrian Speich]

Hi Kristján, 

I have not tried that. How would it look if I wanted to extract the date from a timestamp in the example below?

'Deployment SLA' < 'Started: Approval[Time stamp]'

Deployment SLA format = DD/MM/YYYY

Started: Approval[Time stamp] format = DD/MM/YYYY HH:mm 

Thanks.