calculate the date and time with two custom field

[rTrack Support]

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

[David _old account_ · Accepted solution]

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:

<!-- @@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; -->

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]

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_]

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]

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]

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

[rTrack Support]

@David,

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

[David _old account_]

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:

<!-- @@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  ; -->

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

[David _old account_]

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

<!-- @@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 < 60)
  return diff + " minutes";
diff = diff / 60;
if (diff < 24)
  return diff + " hours";
return (diff / 24) + " days";
-->

[rTrack Support]

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_]

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

David

[rTrack Support]

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]

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]

days.png

[rTrack Support]

i am not sure what logic is used here ..

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

[David _old account_]

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

<!-- @@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 < 60)
  return diff + " minutes";
diff = diff / 60;
if (diff < 24)
  return diff + " hours";
return (diff / 24) + " days";
-->

[rTrack Support]

yah thanks ..

I used that and its working for me .

[rTrack Support]

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_]

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]

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_]

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:

<!-- @@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  ; -->

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

[ldogangul]

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]

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]

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.

[David Fischer]

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]

@David Fischer 

Thanks for the suggestion,

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 

[David Fischer]

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

[shiva]

@David Fischer 

so i should change the output format in script runner ?

[David Fischer]

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

[shiva]

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]

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

[shiva]

@David Fischer 

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

But when i copy what i get is 

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

[Mario]

Hi David,

your solution for calculating minutes:

<!-- @@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  ; -->

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]

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]

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]

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]

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]

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]

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

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

[ldogangul]

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]

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]

Hi 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]

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]

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]

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..

[Deleted user]

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

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

[Vikas Jain]

Hi David, 

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

[David Fischer]

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