-
Suggestion
-
Resolution: Unresolved
-
147
-
92
-
NOTE: This suggestion is for JIRA Cloud. Using JIRA Server? See the corresponding suggestion.
Problem Definition
Jira administrators sometimes need to know the time tickets spent on specific statuses for reporting or auditing purposes. For example, this may be used for checking how long a specific tickets remained in backlog until it was worked on.
Suggested Solution
Add to tickets information about the time spent in each status the tickets has already been in. This could be another tab next to history, for example, that lists all the status the ticket has been to and the amount of time it spent on each. This functionality is available already in some apps.
- is duplicated by
-
- Closed
- is related to
-
- Closed
-
- Closed
-
- Gathering Interest
-
- Gathering Interest
- relates to
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Closed
-
- Gathering Interest
(10 relates to)
[JRACLOUD-63103] Way to collect exact time a issue spend in a workflow - JIRA Cloud
Pinned comments
Jose Luis Gonzalez
added a comment - Hello Everyone,
I would like to contribute by adding one workaround for this query for all users can retrieve the report, although the following options are valid:
Option 1: The days in the column feature in agile boards, show the number of days that an issue has been in a column, you can check this document for more information. To enable it, open the board settings, go to card layout and find the option at the bottom.
Option 2: There are some apps in the marketplace that provide this feature, you could take a look at these apps.
This is the python script:
# file timeInStatus.py
# Requirements: install python3
# How to run: python timeInStatus.py
import requests
from requests.auth import HTTPBasicAuth
import json
import csv
from datetime import datetime,timezone
import re
import urllib.parse
jql= "project = KN4" # insert here the jql that you can test in the UI
APItoken= "APIToken" # generate it here https: //id.atlassian.com/manage-profile/security/api-tokens
AdminEmail= "email@domain.com"
tenant= "site-url.atlassian.net"
# First, get the data of the issues via API
auth = HTTPBasicAuth(AdminEmail, APItoken)
headers = {
"Accept" : "application/json"
}
url = "https: //" +tenant+ "/ rest /api/3/search?jql=" +urllib.parse.quote(jql)+ "&startAt=0&maxResults=100&expand=changelog"
response = requests.request( "GET" , url, headers=headers, auth=auth)
jsonResponse = json.loads(response.text)
# Then, process the data of the issues
data = ""
headers = [ "time0" , "time1" , "status" , "deltaSec" , "key" ]
# output csv file
with open( 'logs.csv' , 'w' ) as f:
writer = csv.DictWriter(f, fieldnames=headers)
writer.writeheader()
# process each retrives issue
for issue in jsonResponse[ "issues" ]:
statusChanges = []
# sincethe current status doesn't have history tiem, we add it for calculation
print( "working with:" + issue[ "key" ])
statusChange = {}
statusChange[ "time0" ] = datetime.fromisoformat(re.sub(r '00$' , ':00' ,issue[ "fields" ][ "created" ]))
statusChange[ "time1" ] = datetime.now(timezone.utc).astimezone()
statusChange[ "status" ] = issue[ "fields" ][ "status" ][ "name" ]
statusChange[ "key" ] = issue[ "key" ]
statusChanges.append(statusChange)
# Process the changelog items from the newest to the oldest
for i in range(len(issue[ "changelog" ][ "histories" ])):
history = issue[ "changelog" ][ "histories" ][i]
for item in history[ "items" ]:
if item[ "field" ] == "status" :
statusChange = {}
statusChange[ "key" ] = issue[ "key" ]
statusChange[ "time1" ] = datetime.fromisoformat(re.sub(r '00$' , ':00' , history[ "created" ]))
statusChange[ "status" ] = item[ "fromString" ]
## adjust previous status
statusChanges[len(statusChanges)-1][ "time0" ] = datetime.fromisoformat(re.sub(r '00$' , ':00' ,history[ "created" ]))
statusChanges.append(statusChange)
#print(history[ "created" ],history[ "items" ][0][ "field" ], history[ "items" ][0][ "fromString" ],history[ "items" ][0][ "toString" ])
# complete the first history entry, the start date is when the issue was created
statusChanges[len(statusChanges)-1][ "time0" ] = datetime.fromisoformat(re.sub(r '00$' , ':00' ,issue[ "fields" ][ "created" ]))
for statusChange in statusChanges:
data = data + "[ '" +issue[ "key" ]+ "' , '" +statusChange[ "status" ]+ "' , new Date(" + str( int (statusChange[ "time0" ].timestamp()*1000)) + "), new Date(" +str( int (statusChange[ "time1" ].timestamp()*1000))+ ")],\n"
statusChange[ "deltaSec" ] = (statusChange[ "time1" ] - statusChange[ "time0" ]).total_seconds()
writer.writerows(statusChanges)
# Write the html presentation template, it uses google charts api
template = "<script type= 'text/javascript' src= 'https: //www.gstatic.com/charts/loader.js' ></script>\n\
<script type= 'text/javascript' > \n\
google.charts.load( 'current' , {\n\
packages: [ 'timeline' ]\n\
}).then(function () {\n\
var container = document.getElementById( 'timeline' );\n\
var chart = new google.visualization.Timeline(container);\n\
var dataTable = new google.visualization.DataTable();\n\
dataTable.addColumn({ type: 'string' , id: 'Federation' });\n\
dataTable.addColumn({ type: 'string' , id: 'Event' });\n\
dataTable.addColumn({ type: 'date' , id: 'Start' });\n\
dataTable.addColumn({ type: 'date' , id: 'End' });\n\
dataTable.addRows([\n\
%DATA%]);\n\
var options = {\n\
timeline: { colorByRowLabel: true },\n\
tooltip: {isHtml: true },\n\
legend: 'none' ,\n\
backgroundColor: '#ffd' \n\
};\n\
\n\
var formatTime = new google.visualization.DateFormat({\n\
pattern: 'yyyy-MMM-dd HH:mm:ss a' \n\
});\n\
\n\
var view = new google.visualization.DataView(dataTable);\n\
view.setColumns([0, 1, {\n\
role: 'tooltip' ,\n\
type: 'string' ,\n\
calc: function (dt, row) {\n\
// build tooltip\n\
var dateBegin = dt.getValue(row, 2);\n\
var dateEnd = dt.getValue(row, 3);\n\
var oneHour = (60 * 60 * 1000);\n\
var duration = (dateEnd.getTime() - dateBegin.getTime()) / oneHour;\n\
\n\
var tooltip = '<div><div class=\ "ggl-tooltip\" ><span>' ;\n\
tooltip += dt.getValue(row, 0) + ':</span> ' + dt.getValue(row, 1) + '</div>' ;\n\
tooltip += '<div class=\ "ggl-tooltip\" ><div>' + formatTime.formatValue(dateBegin) + ' - ' ;\n\
tooltip += formatTime.formatValue(dateEnd) + '</div>' ;\n\
tooltip += '<div><span>Duration: </span>' + duration + ' hours</div></div>' ;\n\
\n\
return tooltip;\n\
},\n\
p: {html: true }\n\
}, 2, 3]);\n\
\n\
chart.draw(view.toDataTable(), options); // <-- use data view to draw chart\n\
}); </script>\n\
<div id= 'timeline' style= 'height: 600px;' ></div>"
template = template.replace( "%DATA%" ,data)
f = open( "timeline.html" , "w" )
f.write(template)
f.close()
The output is a CSV with the timestamps and statuses:
time0
time1
status
deltaSec
key
2022-10-18 17:03:59.448000-05:00
2023-01-31 14:29:35.583039-06:00
Approved
9066336.135039
SCRUM-1487
2022-10-18 16:49:53.196000-05:00
2022-10-18 17:03:59.448000-05:00
To Do
846.252
SCRUM-1487
2022-10-18 17:05:42.310000-05:00
2023-01-31 14:29:35.583163-06:00
Cancelled
9066233.273163
SCRUM-1486
2022-10-18 17:05:22.638000-05:00
2022-10-18 17:05:42.310000-05:00
To Do
19.672
SCRUM-1486
And an HTML file with a timeline chart
Cheers!
All comments
Hello Everyone,
I would like to contribute by adding one workaround for this query for all users can retrieve the report, although the following options are valid:
Option 1: The days in the column feature in agile boards, show the number of days that an issue has been in a column, you can check this document for more information. To enable it, open the board settings, go to card layout and find the option at the bottom.
Option 2: There are some apps in the marketplace that provide this feature, you could take a look at these apps.
This is the python script:
# file timeInStatus.py # Requirements: install python3 # How to run: python timeInStatus.py import requests from requests.auth import HTTPBasicAuth import json import csv from datetime import datetime,timezone import re import urllib.parse jql="project = KN4" # insert here the jql that you can test in the UI APItoken="APIToken" # generate it here https://id.atlassian.com/manage-profile/security/api-tokens AdminEmail="email@domain.com" tenant="site-url.atlassian.net" # First, get the data of the issues via API auth = HTTPBasicAuth(AdminEmail, APItoken) headers = { "Accept": "application/json" } url = "https://"+tenant+"/rest/api/3/search?jql="+urllib.parse.quote(jql)+"&startAt=0&maxResults=100&expand=changelog" response = requests.request("GET", url, headers=headers, auth=auth) jsonResponse = json.loads(response.text) # Then, process the data of the issues data = "" headers = ["time0","time1","status","deltaSec","key"] # output csv file with open('logs.csv', 'w') as f: writer = csv.DictWriter(f, fieldnames=headers) writer.writeheader() # process each retrives issue for issue in jsonResponse["issues"]: statusChanges = [] # sincethe current status doesn't have history tiem, we add it for calculation print("working with:" + issue["key"]) statusChange = {} statusChange["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"])) statusChange["time1"] = datetime.now(timezone.utc).astimezone() statusChange["status"] = issue["fields"]["status"]["name"] statusChange["key"] = issue["key"] statusChanges.append(statusChange) # Process the changelog items from the newest to the oldest for i in range(len(issue["changelog"]["histories"])): history = issue["changelog"]["histories"][i] for item in history["items"]: if item["field"] == "status": statusChange = {} statusChange["key"] = issue["key"] statusChange["time1"] = datetime.fromisoformat(re.sub(r'00$',':00', history["created"])) statusChange["status"] = item["fromString"] ## adjust previous status statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',history["created"])) statusChanges.append(statusChange) #print(history["created"],history["items"][0]["field"], history["items"][0]["fromString"],history["items"][0]["toString"]) # complete the first history entry, the start date is when the issue was created statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"])) for statusChange in statusChanges: data = data + "['"+issue["key"]+"','"+statusChange["status"]+"', new Date("+ str(int(statusChange["time0"].timestamp()*1000)) + "), new Date("+str(int(statusChange["time1"].timestamp()*1000))+")],\n" statusChange["deltaSec"] = (statusChange["time1"] - statusChange["time0"]).total_seconds() writer.writerows(statusChanges) # Write the html presentation template, it uses google charts api template = "<script type='text/javascript' src='https://www.gstatic.com/charts/loader.js'></script>\n\ <script type='text/javascript'> \n\ google.charts.load('current', {\n\ packages: ['timeline']\n\ }).then(function () {\n\ var container = document.getElementById('timeline');\n\ var chart = new google.visualization.Timeline(container);\n\ var dataTable = new google.visualization.DataTable();\n\ dataTable.addColumn({ type: 'string', id: 'Federation' });\n\ dataTable.addColumn({ type: 'string', id: 'Event' });\n\ dataTable.addColumn({ type: 'date', id: 'Start' });\n\ dataTable.addColumn({ type: 'date', id: 'End' });\n\ dataTable.addRows([\n\ %DATA%]);\n\ var options = {\n\ timeline: { colorByRowLabel: true },\n\ tooltip: {isHtml: true},\n\ legend: 'none',\n\ backgroundColor: '#ffd'\n\ };\n\ \n\ var formatTime = new google.visualization.DateFormat({\n\ pattern: 'yyyy-MMM-dd HH:mm:ss a'\n\ });\n\ \n\ var view = new google.visualization.DataView(dataTable);\n\ view.setColumns([0, 1, {\n\ role: 'tooltip',\n\ type: 'string',\n\ calc: function (dt, row) {\n\ // build tooltip\n\ var dateBegin = dt.getValue(row, 2);\n\ var dateEnd = dt.getValue(row, 3);\n\ var oneHour = (60 * 60 * 1000);\n\ var duration = (dateEnd.getTime() - dateBegin.getTime()) / oneHour;\n\ \n\ var tooltip = '<div><div class=\"ggl-tooltip\"><span>';\n\ tooltip += dt.getValue(row, 0) + ':</span> ' + dt.getValue(row, 1) + '</div>';\n\ tooltip += '<div class=\"ggl-tooltip\"><div>' + formatTime.formatValue(dateBegin) + ' - ';\n\ tooltip += formatTime.formatValue(dateEnd) + '</div>';\n\ tooltip += '<div><span>Duration: </span>' + duration + ' hours</div></div>';\n\ \n\ return tooltip;\n\ },\n\ p: {html: true}\n\ }, 2, 3]);\n\ \n\ chart.draw(view.toDataTable(), options); // <-- use data view to draw chart\n\ }); </script>\n\ <div id='timeline' style='height: 600px;'></div>" template = template.replace("%DATA%",data) f = open("timeline.html", "w") f.write(template) f.close()
The output is a CSV with the timestamps and statuses:
| time0 | time1 | status | deltaSec | key |
| 2022-10-18 17:03:59.448000-05:00 | 2023-01-31 14:29:35.583039-06:00 | Approved | 9066336.135039 | SCRUM-1487 |
| 2022-10-18 16:49:53.196000-05:00 | 2022-10-18 17:03:59.448000-05:00 | To Do | 846.252 | SCRUM-1487 |
| 2022-10-18 17:05:42.310000-05:00 | 2023-01-31 14:29:35.583163-06:00 | Cancelled | 9066233.273163 | SCRUM-1486 |
| 2022-10-18 17:05:22.638000-05:00 | 2022-10-18 17:05:42.310000-05:00 | To Do | 19.672 | SCRUM-1486 |
And an HTML file with a timeline chart
Cheers!
All comments
Hello Everyone,
I would like to contribute by adding one workaround for this query for all users can retrieve the report, although the following options are valid:
Option 1: The days in the column feature in agile boards, show the number of days that an issue has been in a column, you can check this document for more information. To enable it, open the board settings, go to card layout and find the option at the bottom.
Option 2: There are some apps in the marketplace that provide this feature, you could take a look at these apps.
This is the python script:
# file timeInStatus.py # Requirements: install python3 # How to run: python timeInStatus.py import requests from requests.auth import HTTPBasicAuth import json import csv from datetime import datetime,timezone import re import urllib.parse jql="project = KN4" # insert here the jql that you can test in the UI APItoken="APIToken" # generate it here https://id.atlassian.com/manage-profile/security/api-tokens AdminEmail="email@domain.com" tenant="site-url.atlassian.net" # First, get the data of the issues via API auth = HTTPBasicAuth(AdminEmail, APItoken) headers = { "Accept": "application/json" } url = "https://"+tenant+"/rest/api/3/search?jql="+urllib.parse.quote(jql)+"&startAt=0&maxResults=100&expand=changelog" response = requests.request("GET", url, headers=headers, auth=auth) jsonResponse = json.loads(response.text) # Then, process the data of the issues data = "" headers = ["time0","time1","status","deltaSec","key"] # output csv file with open('logs.csv', 'w') as f: writer = csv.DictWriter(f, fieldnames=headers) writer.writeheader() # process each retrives issue for issue in jsonResponse["issues"]: statusChanges = [] # sincethe current status doesn't have history tiem, we add it for calculation print("working with:" + issue["key"]) statusChange = {} statusChange["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"])) statusChange["time1"] = datetime.now(timezone.utc).astimezone() statusChange["status"] = issue["fields"]["status"]["name"] statusChange["key"] = issue["key"] statusChanges.append(statusChange) # Process the changelog items from the newest to the oldest for i in range(len(issue["changelog"]["histories"])): history = issue["changelog"]["histories"][i] for item in history["items"]: if item["field"] == "status": statusChange = {} statusChange["key"] = issue["key"] statusChange["time1"] = datetime.fromisoformat(re.sub(r'00$',':00', history["created"])) statusChange["status"] = item["fromString"] ## adjust previous status statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',history["created"])) statusChanges.append(statusChange) #print(history["created"],history["items"][0]["field"], history["items"][0]["fromString"],history["items"][0]["toString"]) # complete the first history entry, the start date is when the issue was created statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"])) for statusChange in statusChanges: data = data + "['"+issue["key"]+"','"+statusChange["status"]+"', new Date("+ str(int(statusChange["time0"].timestamp()*1000)) + "), new Date("+str(int(statusChange["time1"].timestamp()*1000))+")],\n" statusChange["deltaSec"] = (statusChange["time1"] - statusChange["time0"]).total_seconds() writer.writerows(statusChanges) # Write the html presentation template, it uses google charts api template = "<script type='text/javascript' src='https://www.gstatic.com/charts/loader.js'></script>\n\ <script type='text/javascript'> \n\ google.charts.load('current', {\n\ packages: ['timeline']\n\ }).then(function () {\n\ var container = document.getElementById('timeline');\n\ var chart = new google.visualization.Timeline(container);\n\ var dataTable = new google.visualization.DataTable();\n\ dataTable.addColumn({ type: 'string', id: 'Federation' });\n\ dataTable.addColumn({ type: 'string', id: 'Event' });\n\ dataTable.addColumn({ type: 'date', id: 'Start' });\n\ dataTable.addColumn({ type: 'date', id: 'End' });\n\ dataTable.addRows([\n\ %DATA%]);\n\ var options = {\n\ timeline: { colorByRowLabel: true },\n\ tooltip: {isHtml: true},\n\ legend: 'none',\n\ backgroundColor: '#ffd'\n\ };\n\ \n\ var formatTime = new google.visualization.DateFormat({\n\ pattern: 'yyyy-MMM-dd HH:mm:ss a'\n\ });\n\ \n\ var view = new google.visualization.DataView(dataTable);\n\ view.setColumns([0, 1, {\n\ role: 'tooltip',\n\ type: 'string',\n\ calc: function (dt, row) {\n\ // build tooltip\n\ var dateBegin = dt.getValue(row, 2);\n\ var dateEnd = dt.getValue(row, 3);\n\ var oneHour = (60 * 60 * 1000);\n\ var duration = (dateEnd.getTime() - dateBegin.getTime()) / oneHour;\n\ \n\ var tooltip = '<div><div class=\"ggl-tooltip\"><span>';\n\ tooltip += dt.getValue(row, 0) + ':</span> ' + dt.getValue(row, 1) + '</div>';\n\ tooltip += '<div class=\"ggl-tooltip\"><div>' + formatTime.formatValue(dateBegin) + ' - ';\n\ tooltip += formatTime.formatValue(dateEnd) + '</div>';\n\ tooltip += '<div><span>Duration: </span>' + duration + ' hours</div></div>';\n\ \n\ return tooltip;\n\ },\n\ p: {html: true}\n\ }, 2, 3]);\n\ \n\ chart.draw(view.toDataTable(), options); // <-- use data view to draw chart\n\ }); </script>\n\ <div id='timeline' style='height: 600px;'></div>" template = template.replace("%DATA%",data) f = open("timeline.html", "w") f.write(template) f.close()
The output is a CSV with the timestamps and statuses:
| time0 | time1 | status | deltaSec | key |
| 2022-10-18 17:03:59.448000-05:00 | 2023-01-31 14:29:35.583039-06:00 | Approved | 9066336.135039 | SCRUM-1487 |
| 2022-10-18 16:49:53.196000-05:00 | 2022-10-18 17:03:59.448000-05:00 | To Do | 846.252 | SCRUM-1487 |
| 2022-10-18 17:05:42.310000-05:00 | 2023-01-31 14:29:35.583163-06:00 | Cancelled | 9066233.273163 | SCRUM-1486 |
| 2022-10-18 17:05:22.638000-05:00 | 2022-10-18 17:05:42.310000-05:00 | To Do | 19.672 | SCRUM-1486 |
And an HTML file with a timeline chart
Cheers!
I'm so disappointed that I couldn't find this feature natively in Jira.
I hope that will change quickly
Jira is a Ticket Tracking System without Ticket tracking features. This is a very known approach by Atlassian to sell marketplace products and forcing companies to pay more money.
ANY UPDATE ON THIS?
I use the free version of "Timentify" BUT still to get more valuable report you need to buy pro version.
I believe this is should be built in Feature at Jira
I can't believe this is not built-in in JIRA. All the data are available, but not no way do a clean report with with.
As Bloompeak, we developed below 2 apps available at Atlassian Marketplace.
- Status Time: It generates status time report based on a calendar(7/24 or your working calendar). You can see sample report on online demo here.
- Status Time Free : Limited but free version of the above.
Our team at OBSS created Time in Status for this exact need:
https://marketplace.atlassian.com/apps/1211756/time-in-status?hosting=cloud&tab=overview
It can show how much time each issue spent on each status, assigned to each user or assigned to a member of each group.
Data can be exported as CSV or Excel.
There are addons for this, like ours https://marketplace.atlassian.com/apps/1218809/quantify that can provide workflow analytics. Works for cloud, next-gen projects and with support.
The Time in Status for Jira app offers the following functionality.
7 report types:
All reports can be generated by setting up a work schedule in the application, excluding weekends and non-working hours.