Jira Agile non-active sprint creator and created date

[Vidic Florjan]

Is possible to get information about creator or created date of non-active sprint?

(In jira Agile user interface or in database, logs, by JQL statements,...)

[Shayne · Accepted solution]

I believe the follow SQL query may return what you're after:

SELECT username,lastviewed,data FROM userhistoryitem WHERE data = 'Name of Sprint';

Based on some limited testing, the 'lastviewed' entry gives the date/time the sprint was created in milliseconds and username reflects who created the sprint.

Edit: I usually use http://www.epochconverter.com/ to convert milliseconds to something human-readable. 

[Vidic Florjan]

Thank you! Florjan The last version of the sql query (oracle): select id, username, data sprint_name, to_timestamp_tz('1970-01-01 Europe/Vienna', 'yyyy-mm-dd tzr')+ numtodsinterval(lastviewed/1000,'second') created from userhistoryitem where dbms_lob.compare(data, to_clob('2015 01-02')) = 0;

[Vidic Florjan]

'2015 01-02' is my sprint name.

[fzhang3]

this really helps a lot.

[Leonard Chew]

userhistorydata is tricky. It only stores one record per user and sprint. As long as the creator is the only person editing the sprint name, the changed_timestamp will remain the creation date.

more info and for a more complex sql see also: https://community.atlassian.com/t5/Jira-questions/SQL-How-can-I-find-who-opened-a-Sprint/qaq-p/588838#M364070

[Balvant Biradar]

Hi All,

Below query can be used for finding the details of Closed date in Human readable format in MySql database

select ID,Name, FROM_UNIXTIME(Start_DATE/1000),FROM_UNIXTIME(COMPLETE_DATE/1000) as completed_dates from AO_60DB71_SPRINT where  FROM_UNIXTIME(COMPLETE_DATE/1000) >= '2015-06-01' and closed = 1;

Actually in Sprint table date's are stored in Big Int format(20 digits may be) so need to change it using FROM_UNIXTIME