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

calculate the date and time with two custom field

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

2 votes
Answer accepted

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.

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

 

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.

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

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

@David,

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

 

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.

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;

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 .

 

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

David

Thanks David .

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

Thanks for ur 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";
-->

Like Mohamed El Taweel likes this

i am not sure what logic is used here ..

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

 

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;

yah thanks ..

I used that and its working for me .

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 . 

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.

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 .

 

 

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

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

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

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.

Hi David, 

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

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

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

 

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

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

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. 

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

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

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

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

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

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!

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,

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

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

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

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

@David Fischer _Innovalog_ 

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

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

@David Fischer _Innovalog_ 

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

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

@David Fischer _Innovalog_ 

so i should change the output format in script runner ?

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

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

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

Suggest an answer

Log in or Sign up to answer
TAGS

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