mysql - What is the easiest way to extract sprint start and end dates from the JIRA db?

Question

I'm trying to extract the start and end days of my sprints from the Jira database. This would seem like a simple task, but it really (as far as I have found out at least) is not.

While trying to figure this out I found one solution, but it seems to me so cumbersome and difficult that I have problems thinking this is the only way.

Here is what I have found out:

Sprints are not a native Jira expression and the Greenhopper plugin uses the projectversion table to represent sprints.

The projectversion table contains some information about the sprint, like name, what project it belongs to and the release date. The release date can be thought of as a sprint end date, but the start date is missing.

If you run back to back sprints maybe the start date of a sprint can be set to the release date of the previous sprint plus one day? But that is really not a good solution.

So I searched through the Jira data model and the best and only reference I found to a sprint start date was in the property structure.

You can define properties and assign them values. In the main table of this structure, the propertyentry table, I found entries like this:

ID     ENTITY_NAME     ENTITY_ID     PROPERTY_KEY                          propertytype    
-----  --------------  ------------  ------------------------------------  ------------
 10288  GreenHopper     10010         CONFIGURATION                         6               
 10304  GreenHopper     10012         CONFIGURATION                         6               
 10312  GreenHopper     10013         CONFIGURATION                         6               
 10449  GreenHopper     10014         CONFIGURATION                         6   

So GreenHopper have added a property with the key set to CONFIGURATION. The etity_id field references project.id and the configuration property is a project configuration. The property_type is set to 6 which tells us to look for the value in the propertytext table.

The value stored in the propertytext table reveals it self as a XML string containing different information about the project, among it entries like this:

<entry>
  <string>BURNDOWN_START_DATE_10074</string>
  <long>1316988000000</long>
</entry> 

There it is. The best equivalent I have found to a sprint start date. Hidden in an xml string in a property table.

My question is: Is this really the only way to find my sprint starting dates?

Answer 1

There seems to be no way of getting end and start date of sprint via Jira SOAP/REST API.

You can extract the start and end dates of your sprints using: com.pyxis.greenhopper.jira.configurations.ProjectConfiguration#getVersionStartDate com.pyxis.greenhopper.jira.configurations.ProjectConfiguration#getVersionEndDate

To use this class you can write a Jira plugin - Developing with the Atlassian Plugin SDK

Another option is to write GreenHopper module - GreenHopper Developer Documentation