calculate the date and time with two custom field

rTrack Support October 19, 2016

Hi  ,

our requirement is to find the difference between the two date time custom fields .is it possible ??

if possible please provide me the solution asap .

i have installed JIRA misc custom fields and i tried with the below expression .But that is not working for me .

 

Examole : Field 1 :created date bs (customfield_19700)

Field 2 : Closed Date bs  (customfield_19716)

i need to find how the time taken to close the issue from the created date field .

these are JIRA custom field date time picker .

and also if it is possible with created and resolved date field 

 

 

<!-- @@Formula: issue.get("customfield_19716")==null ? null : (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24 -->

 

7 answers

1 accepted

3 votes
Answer accepted
David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2016

I assume you created a Calculated Number field, correct?

There is a small error in your formula, you need to check both fields for null values:

&lt;!-- @@Formula: 
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24; --&gt;

And of course you could also use the created or resolved fields, see https://innovalog.atlassian.net/wiki/display/KB/Using+issue.get%28%3Cfield_name%3E%29+in+scripts for details.

rTrack Support October 20, 2016

Hi david ,

i accept this .

But i have a small query ..

i checked with the field but it does not showing any values .

fyr 

i attached the screen shots ..

 

Field Type :Calculated Date time field 

search Template : date range picker 

<!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24; -->

filter.pngdate.png

But the field does not appearing in the screen 

i have mapped the field in the screen .

Please help me on this ..

 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2016

As I already said, it needs to be a calculated NUMBER field. You are returning a number (the number of days between two dates), not a Date.

rTrack Support October 20, 2016

Yah its working fine .

Is it possible to take if the hours is 0 then need to show the minutes .

shall i do it by using the below script .

<!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600 / 24 / 60  ; -->

rTrack Support October 20, 2016

And I missed to say Thanks for the valuable  immediate support .

rTrack Support October 20, 2016

@David,

please help on this .we need to calculate the time between the two fields .

 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2016

Well, the problem is, how are you going to know whether it's days, hours or minutes you're returning?

If you only return minutes, then your formula should be:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 60  ; --&gt;

If you want to return hours, you should further divide by 60.

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2016

And if you instead want to return a string describing the difference, then you'll need to create a Calculated Text field instead:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
diff = issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime() / 1000 / 60;
if (diff &lt; 60)
  return diff + " minutes";
diff = diff / 60;
if (diff &lt; 24)
  return diff + " hours";
return (diff / 24) + " days";
--&gt;
rTrack Support October 20, 2016

Hi David ,

i have some other questions also .

is it possible to calculate the time based on business working hours .

For example :i need to take the time between 9 am to 6 pm and weekdays from monday to friday only .

if created date or time is non working then i need to calculate only the working hours time only .

if u are not understanding my questions please let me know .

 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2016

I think someone posted a solution to this some time ago. Search the answers, because it is fairly complicated.

David

rTrack Support October 20, 2016

Thanks David .

I hope i can get the answers . if i need any other helps , then i will ask here ..

Thanks for ur support .

rTrack Support October 20, 2016

Hi David ,

i created the issue at the same time i resolved .the custom field have the values .but it results something different .can you please help on this .

but it resilts with 1025718807 days

 

i used to created the Calculated Textfield  .


script i used :

 

<!-- @@Formula:
if (issue.get("customfield_19736")==null || issue.get("customfield_19735") == null)
return null;
diff = issue.get("customfield_19736").getTime() - issue.get("customfield_19735").getTime() / 1000 / 60 / 60;
if (diff < 60)
return diff + " minutes";
diff = diff / 60;
if (diff < 24)
return diff + " hours";
return (diff / 24) + " days";
-->

rTrack Support October 20, 2016

days.png

Like Mohamed El Taweel likes this
rTrack Support October 20, 2016

i am not sure what logic is used here ..

If possible explain then i can manage it own for improved tasks .

 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2016

Sorry, I forgot parentheses in the "diff" calculation:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
diff = (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 60;
if (diff &lt; 60)
  return diff + " minutes";
diff = diff / 60;
if (diff &lt; 24)
  return diff + " hours";
return (diff / 24) + " days";
--&gt;
rTrack Support October 21, 2016

yah thanks ..

I used that and its working for me .

rTrack Support October 21, 2016

I have another questions .

Is it possible to filter the datas in jquery for those custom fields .

i have to take the report for ageing analysis .

so i have to take the report which are resolved less than 2 hrs , less than 1 day , more than 3 days , more than 7 days . is it possible to do with using jql filters . 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2016

You'll need to use your first calculated number field for that - not easy to do comparisons with text. But easy to do with a number of hours for example (7 days = 7x24 hours...)

Don't forget to re-index JIRA after creating your calculated fields.

rTrack Support October 23, 2016

Hi , is these fields will display for the JIRA charts .

i have checked with pie charts  but it is not showing .

can u please let me know if it is possible .

David ,

in the last thing you have said that it is possible with do with hours .. but i am not sure .how can i do that . can u give me the detailed explanation .

 

 

David _old account_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 23, 2016

You cannot use Calculated Number or Date Fields in most JIRA charts (because of a limitation in JIRA), only Calculated Text Fields.

As for the hours, just create a "HoursSpent" Calculated Number Field with the following formula:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 3600  ; --&gt;

which you can then use in JQL ( "HoursSpent<2" for less that two hours,  "HoursSpent<72" for less that three days, etc.)

ldogangul December 6, 2017

David, 

That helped me too much. Thanks for this post.

I can calculate the time between two customfileds value. 

I need one further step. I need to calculate the time between one custom field and issue created time. How can I do this ?

I tried those but did not worked:

1.

<!-- @@Formula:

if (issue.get("customfield_11804")==null || issue.getCreated() == null)

  return null;

diff = (issue.get("customfield_11804").getTime() - issue.getCreated().getTime()) / 1000 / 60;

if (diff < 60)

  return diff + " minutes";

diff = diff / 60;

if (diff < 24)

  return diff + " hours";

return (diff / 24) + " days";

-->

 

2.

<!-- @@Formula:

if (issue.get("customfield_11804")==null || issue.get(“created”).getTime() == null)

  return null;

diff = (issue.get("customfield_11804").getTime() - issue.get(“created”).getTime()) / 1000 / 60;

if (diff < 60)

  return diff + " minutes";

diff = diff / 60;

if (diff < 24)

  return diff + " hours";

return (diff / 24) + " days";

-->

 

Any help will be appreciated. Best Regards,

Levent

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
December 6, 2017

Hi Levent,

the second formula should work (although I'm not quite sure about adding a string to a number like you did). Did you check atlassian-jira.log for any error? If there is an error with the formula (such as a syntax error), it will show up in the logs.

Of course, this formula is for a calculated text field, right?

David

ldogangul December 7, 2017

Hi David,

Yes it's calculated text field. I have something wrong but can not find out. As my custom filed indexes get some kind of corrupted after creating so many custom fileds at one. I am fixing those errors then I will look at the formula whetget get works or not. 

 

Thanks for your help. I hope to give you good news later on.

0 votes
David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 11, 2020

Hi @shiva ,

you have created a Scripted (Groovy) Duration calculated field. The internal representation of duration fields is in seconds. The display on the issue view is calculated at display time. This explains why you're getting a number (of seconds) when copying from that field.

To copy the duration string value to another field, you'll need to use the Set Field Value post-function instead, and use this Groovy script as the value:

def cfType = ComponentAccessor.customFieldManager.getCustomFieldObject("customfield_10400").customFieldType
cfType.durationFormatter.format(issue.get("customfield_10400"), "pretty-duration")

where customfield_10400 is the custom field ID of the Time-to-resolve field, and "pretty-duration" can be replaced with "worklog-duration" if you want work duration instead of calendar duration. You can also replace the format method with formatShort to get a short version of the string (e.g. "14h 24m")

shiva November 11, 2020

@David Fischer 

Thanks for the suggestion,
image.png

I have the post function, the section in the field= "Time-to-resolve(())" is the text field im trying to copy the value of the scripted field (( Time-to -resolve) ( customfield_22306)

And after the transition the error i get when transitioning is 
image.png

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 12, 2020

@shiva the error is not coming from that JMCF calculated field but from a ScriptRunner scripted field

shiva November 12, 2020

@David Fischer 

so i should change the output format in script runner ?

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 12, 2020

I don't know - you need to fix the Groovy script in that ScriptRunner calculated field. 

shiva November 12, 2020

The scripted field script i use is below, can you have a look.

=====================================================

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.history.ChangeItemBean
import com.atlassian.jira.issue.Issue;
import com.atlassian.core.util.DateUtils

def changeHistoryManager = ComponentAccessor.getChangeHistoryManager()

def inProgressName = "In Progress"

List<Long> rt = [0L]

def changeItems = changeHistoryManager.getChangeItemsForField(issue, "status")
changeItems.reverse().each { ChangeItemBean item ->

def timeDiff = System.currentTimeMillis() - issue.getCreated().getTime()

if (item.fromString == inProgressName) {
rt << -timeDiff
}

if (item.toString == inProgressName) {
rt << timeDiff
}


}

//return (Math.round((rt.sum() as Long) / 3600000 / 24)) as Double

//DateUtils.getDurationString(Math.round(rt.sum() / 1000))

def total = rt.sum() as Long
return (total / 1000) as long ?: 0L

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 12, 2020

I don't see any problem in that script. I would recommend you reach out to Adaptavist support for help with that. 

0 votes
shiva November 10, 2020

@David Fischer 

I Have created a scripted field , i need to copy the field value to any text field, image.png

But when i copy what i get is image.png

How do i get the value of " Time-to-resolve" value copied to the text field or any field .

0 votes
Mario October 7, 2020

Hi David,

your solution for calculating minutes:

&lt;!-- @@Formula:
if (issue.get("customfield_19716")==null || issue.get("customfield_19700") == null)
  return null;
return (issue.get("customfield_19716").getTime() - issue.get("customfield_19700").getTime()) / 1000 / 60  ; --&gt;

works perfectly fine for me. But now I have another use case. How do I integrate e.g. the systemfield "Created" into that formula? All my attempts so far failed unfortunately.

Thank you very much.

Kind regards
Mario

ldogangul October 7, 2020

Hi Mario;

Try this code into your customfields...

<!-- @@Formula:
if (issue.get("customfield_13721") == null) return null;
return ((Double)(issue.get("customfield_13721").getTime() - issue.get("created").getTime())) / 1000 / 3600 / 24 -->

 

Mine works..

Mario October 7, 2020

Thx a lot for the ultra fast feedback.

I'm getting the best results with this code:

if (issue.get("customfield_11411")==null || issue.get("created") == null)
return null;
return (issue.get("customfield_11411").getTime() - issue.get("created").getTime()) / 1000 / 60;

Unfortunately the BigDecimal Output doesnt look too good. Is there some standard expression to be used in the calculted number field for "Format Expression" to get only rounded full numbers? Or ofc some other place where this can be set?

Thanks again!

ldogangul October 7, 2020

I wonder that too. I get decimal numbers referenced by dots. It would be better if I could have get whole rounded number..

Best Regards,

Mario October 7, 2020

Thx again. Just found it and it is really easy.

Just add in the configuration of the calculated number field for "Format expression" this:

numberTool.format('integer',value)

Then the out put is rounded.

Cheers

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 7, 2020

Using a format expression is one option, but if you actually want the value to always be an integer, you should round the value in the formula itself:

if (issue.get("customfield_11411")==null || issue.get("created") == null)
return null;
return Math.round((issue.get("customfield_11411").getTime() - issue.get("created").getTime()) / 1000 / 60);
Mario October 8, 2020

Thank you for your answer. Switched my field to your suggestion!

If I could I would click "Accept Answer"! ;-)

0 votes
ldogangul February 6, 2018

Hi David & all community,

I have solved my problem. I am using calculted text field. I am not so sure how it's resolved but I guess I had some issues with indexing. I recommend re-index after create or change a custom filed.

 

My sample code is like below:

<!-- @@Formula:
if (issue.get("customfield_11807")==null || issue.get("customfield_11806") == null)
return null;
return (issue.get("customfield_11807").getTime() - issue.get("customfield_11806").getTime()) / 1000 / 3600 / 24; -->

or 

<!-- @@Formula:
if (issue.get("customfield_11808")==null || issue.get("customfield_11807") == null)
return null;
return (issue.get("customfield_11808").getTime() - issue.get("customfield_11807").getTime()) / 1000 / 3600 / 24; -->

 

It works fine. And I cath the time stamps and time duration between status change.

Hope your will work as well.

Best Regards, Levent..

Deleted user February 6, 2018

Hi Levent,

I'm not finding calculted text field in jira's custom field. Do I need to install addon for this or need to enable it ? please assist

Regards
Neeraj

ldogangul February 6, 2018

Calculated_Text_Field.JPGHi Neeraj, when you go to the "Add Custom Field" there is an advanced section. If you select advanced then you need to see "Calculated Text Field".   I guess it is in Jira's standart setup.. If it is not there probably you will need to install an add-on. In my case I am using Jira standalone server edition 6.2 version..  Hope you will find this option. 

Deleted user February 6, 2018

Hi Levent,
thanks for response,   I understand I need to install "Jira Misc Custom Fields "   addon in order to get calculated text field.  I'm also having Jira server on 7.5.2

Regards
Neeraj

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 7, 2018

Hi Levent,

after editing a calculated custom field, you must indeed re-index, as Jira will kindly suggest by the way (in a message box at the top-right corner of the page). Until you re-index, the field value will only be accurate on View Issue pages, not on tabular views nor for JQL searches.

David

ldogangul March 25, 2018

David, thanks for your help. I have another question in a different case. Can you please look at my topic in the link below ? 

https://community.atlassian.com/t5/Jira-questions/How-do-I-get-the-year-in-YYYY-format-on-an-issue-duedate/qaq-p/757971#U757975 

 

Regards, Levent..

0 votes
Deleted user February 6, 2018

Hi,
I have similar requirement.  and trying to implement above said solution but  seems to be an issue for me.  Below is the screenshot from my Jira, request you to please assist

Impact_duration_field.jpg

when I'm hovering mouse over red cross it shows below error

Impact_duration_field_1.jpg

 

0 votes
Vikas Jain January 9, 2018

Hi David, 

If I return a String in 1w 2d 3h format, which searcher can I use?

David Fischer
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 9, 2018

Hi Vikas,

if you're returning a String, it means you're creating a Calculated Text Field. In that case, you have a choice between a Free Text searcher (and thus the "~" operator) and an Exact Text Searcher (and the "=" operator). Obviously, you won't be able to search by duration, since it's not a Duration type field (see https://innovalog.atlassian.net/browse/JMCF-228 for that).

David

Suggest an answer

Log in or Sign up to answer