For those who are interested, our team at OBSS developed Time in Status for Jira Server and Cloud. The app is available on Atlassian Marketplace.

https://marketplace.atlassian.com/apps/1211756/time-in-status?hosting=cloud

• The app reports on how much time each issue spent in each status, each assignee or each group.
• Can show durations as days, hours, minutes or seconds.
• Can report based on a 24/7 calendar or custom business calendars you define.
• Can also report the number of times each status and each transition was used.
• Can calculate average durations or counts, grouped by the fields you select.
• Can export report data as CSV, XLS or XLSX.

Better Excel Plugin exports any sort of JIRA data, including status changes and workflow transitions, for custom Excel reporting.

For this use case, you should export the issue workflow transitions with:

• their exact date and time
• start and end status
• duration spent in the start status
• the user who took that transition
• etc.

Having that list of denormalized data in a worksheet, you can apply Excel's standard features to aggregate it to pivot tables and pivot charts or whatever reports you need.
Also, you can define a calculated column with Excel functions for SLA: "if issue type is ticket and time in open status is longer than 4 hours, then 0, otherwise 1".
Please note that all these will be part of your Excel template, which will be merged again and again with the JIRA data at the export time. So, you don't need to repeatedly re-create the pivot charts, but set them up once and fill them with the most current workflow data any time with a single click.

In fact, the plugin comes with a default "workflow / SLA analysis" template that takes this exact approach. It should be straight-forward to customize that to your needs after reading the customization guide.

JIRA Service Desk has a concept of SLAs that allow tracking issue lifecycle time exactly for the scenario mentioned in the issue. In addition, there are other plugins referenced above that can help with charting time taken on issue transitions. Closing this issue now.

Hi,

As native JIRA reporting is a kind of restricted, Valiantys Corporation proposes the PowerReport plugin for JIRA which allows you to make powerfull reporting on all JIRA data !

PowerReport uses the open source Eclipse-based reporting system BIRT to generate compelling and custom reports !

You can have a look at this plugin here => https://marketplace.atlassian.com/plugins/com.valiantys.jira.plugins.reporting

Hoping you would be interested in, I wish you a good day !

Brice

You should have look at the new JQL features of JIRA 5.0 (http://confluence.atlassian.com/display/JIRA/Advanced+Searching). The new JIRA version allows to execute queries like status changed AFTER startOfDay() and similar ones.

Additionally you should keep in mind JQL is a issue query language. Unlike SQL it is thought to find issues and not to select single fields of an issue.

Has any more thought been given on this feature? Not much progress has happened since '07.

We needed to extract this data urgently, in case others find it useful, here is a half baked, quick and dirty SQL query that does the job. You need to know the project id num which should be easy to find out and add to the query.

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_findStatusDate` $$
CREATE DEFINER=`yourJiraDBname`@`%` PROCEDURE `sp_findStatusDate`(
 IN p_project_num VARCHAR (45)
)
BEGIN

  SELECT
    a.`pkey`,
    a.`issuestatus`,
    b.`STEP_ID`,
    b.`START_DATE`,
    b.`FINISH_DATE`,
    b.`ENTRY_ID`,
    b.`CALLER`
  FROM os_historystep b
    INNER JOIN jiraissue a on a.`WORKFLOW_ID` = b.`ENTRY_ID`
  WHERE (a.`PROJECT` = p_project_num)
  ORDER BY a.`pkey`;

END $$

DELIMITER ;

The HP/Palm Jira Search Plugin has an option that will search the entire issue change history, including who changed the field and when it was changed.

+ 1 too please. We use Jira as Support Management system and require to be report on the transition status time

+1 for this as well. I have 32 status along our workflow and have no idea the time between statuses. Help