Script to calculate total time between states.

[pratik_kumar]

Hi All,

I want a script which should calculate, he elapsed time of a task   i.e. the time spent between the state "To be started" and the state "Completed" and i don't want to add state"pending " time.

These are the "sc_task" state.

[Chuck Tomasi]

Hi Pratik,

There is a built in platform feature called Metrics that can do this for you. I invite you to take a look.

If you want to do this via script, then you can certainly do it using the this method.

Example:

// Given two date/times as DateTime objects
// Set the values this way to ensure a consistent input time
var date1 = new GlideDateTime();
var date2 = new GlideDateTime();
date1.setDisplayValueInternal('2014-01-01 12:00:00');
date2.setDisplayValueInternal('2014-01-01 13:00:00');
 
// Determine the difference as number of seconds (returns a string)
// Use getDisplayValue() to convert the string to the format expected by dateDiff()
var diffSeconds = gs.dateDiff(date1.getDisplayValue(), date2.getDisplayValue(), true);
 
// JavaScript will coerce diffSeconds from a string to a number
// since diffSeconds is being compared to a number
var msg = (diffSeconds <= 0) ? ' is on or after ' : ' is before ';
gs.print(date1.getDisplayValue() + msg + date2.getDisplayValue())

​

[SN Emy]

Hey Chuck,

I need to do the same thing on the Idea and Demand tables.

We use some stats from the task table + new custom stats

The new stats are not taken into consideration.

Do you have an idea? 

[Alec Hanson Bay]

Chuck's code example had gone funny. If anyone is interested this is the readable version

// Given two date/times as DateTime objects
// Set the values this way to ensure a consistent input time

var date1 = new GlideDateTime();
var date2 = new GlideDateTime();

date1.setDisplayValueInternal('2014-01-01 12:00:00');
date2.setDisplayValueInternal('2014-01-01 13:00:00'); 

// Determine the difference as number of seconds (returns a string)
// Use getDisplayValue() to convert the string to the format expected by dateDiff()

var diffSeconds = gs.dateDiff(date1.getDisplayValue(), date2.getDisplayValue(), true); 

// JavaScript will coerce diffSeconds from a string to a number
// since diffSeconds is being compared to a number

var msg = (diffSeconds <= 0) ? ' is on or after ' : ' is before ';
gs.print(date1.getDisplayValue() + msg + date2.getDisplayValue());

[Dubz]

As Chuck has suggested, Metrics will allow you to measure the time spent in each state. If you want to measure the time that has elapsed from when the state was 'to be started and when it went to 'completed' and, furthermore, exclude any time spent 'pending', then your best bet is to configure an SLA.

You can set up your SLA to trigger when the state changes to 'to be started', set up a pause condition for when the state is 'pending' and complete the SLA when the state changes to 'completed'

You can report on your SLA using the task_sla table (assuming you're applying this to a table that extends task, if not you may have to make your own database view or your table and the sla table, details on that here). There are plenty of OOB reports on SLA's that can serve as a template on how best to report on this table.