How can I find out how long a task has been in the original status when updating the status?

[Helen Porter]

Hello 

We set up automatic changes to the DueDate field in tasks. To do this, we need to know how long the task was on a specific status.
How do I find out how long a task has been on a specific status?
How to create a sql query?

How can I find out how long a task has been in the original status when updating the status?

[Helen Porter · Accepted solution]

The question is closed.
Example sql query:

SELECT EXTRACT(DAY FROM (b - a) DAY TO SECOND) FROM (SELECT (SELECT max(CREATED) FROM changeitem JOIN changegroup ON changeitem.groupid=changegroup.id WHERE changeitem.field='status' AND dbms_lob.substr(changeitem.newstring,4096, 1) = 'Open' AND changegroup.issueid = '683789') a , (select trunc (SYSDATE) from dual) b FROM dual);

[Marat]

Hi

If you use PoweScript this can help you
https://confluence.cprime.io/display/TR/Get+time+spent+in+certain+status+per+issue

[Helen Porter]

Hi
Thank you for the information. I'm trying to figure out how to get the right value.
I need to assign a string. Can you help me?
This is the wrong script:

string t;
string[] timeSpentInStatuses = getStatusesTimeSpent("JIRA-14588");
t = timeSpentInStatuses[7];

 The value of the string "t" is empty.