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?

Hypertension

Why do you have to use the database directly? There are options to show views of your sprints (in JIRA versions) ... And if you have the need for the data, I would first try to use the API of JIRA to get the information. – Diligence

I use the database to extract work log information that I have not been able to get through the Jira web application. Like a report of hours logged by each team member per day. I will look into the Jira API and see what it offers. – Hypertension

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

Heartrending

How do you get a Version object reference? – Skiagraph

Actually you can get the start and end date of a sprint using the JIRA Agile REST API with this: /rest/greenhopper/1.0/rapid/charts/sprintreport.json?rapidViewId=652&sprintId=577 and see the sprint element. It also comes with the completed issues in the sprint – Pember

Answer 2

I don't recommend accessing the JIRA database directly if it can be avoided. The undocumented JIRA Agile REST API such as rest/greenhopper/1.0/rapid/charts/sprintreport.json?rapidViewId=652&sprintId=577 where rapidViewId is the board id, gives you the Sprint information. This and other REST resources can be seen in the jira-python library at http://jira-python.readthedocs.org/en/latest/

Pember

The format of the dates is: startDate: 07/Nov/13 3:40 PM endDate: 31/Dec/13 3:40 PM – Pember

Answer 3

SELECT * FROM a0_60db71_sprint

This is a MySQL question, not java.

Connect to your JIRA MySQL Database and look for a table that matches *_sprint

The fields on the above table are:

• Closed (boolean)
• ID (key)
• Start_Date (timestamp)
• End_Date (timestamp)
• Complete_Date (timestamp).

Abie

You are making an assumption that MySQL is the database being used and violating the Active Objects abstraction. – Skiagraph

Answer 4

The easiest way to find start date and end date of sprint in Agile jira is to hit jiraschema.AO_60DB71_SPRINT table. This stores start_date, end_date as big int. Jira for some reasons stores these date formats as int data type. To convert the int to date data type, here is the query in MS Sql. You can easily change it to some other database if required.

###This query pulls start_date, end_date, completed_date of all non active sprints in MS SQL.###

select ID, Name SprintName
,START_DATE / 60 / 60 / 24 / 1000  + CAST('12/31/1969' as datetime)+1 StartDate
,END_DATE / 60 / 60 / 24 / 1000  + CAST('12/31/1969' as datetime)+1 EndDate
,COMPLETE_DATE / 60 / 60 / 24 / 1000  + CAST('12/31/1969' as datetime)+1 CompletedDate
FROM
 AO_60DB71_SPRINT as sprint
where COMPLETE_DATE is not null

Biysk

Can you please explain why there is a +1 on the end of each one, is it to allow for a specific time zone? Also this always shows rounded to the day. – Hamnet

Answer 5

I was given a task recently to get list of sprints with dates for specific project. First I needed to find project ID from Project table and customfield ID for field Sprint from tables customfield/customfieldvalue.

Here is the result

select 
    p.pname as "Project Name",
    s.NAME as "Sprint Name", 
    from_unixtime(s.START_DATE / 1000) as "Start Date", 
    from_unixtime(s.END_DATE / 1000) as "End Date",
    from_unixtime(s.COMPLETE_DATE / 1000 ) as "Complete Date"
from
    customfieldvalue as c, 
    jiraissue as i, 
    project as p, 
    AO_60DB71_SPRINT as s
where 
    p.id = <project ID> and p.id = i.project and
    c.issue = i.id and c.customfield = <customfield ID> and
    c.stringvalue = s.id
group by s.name 
;

Our mysql server is in different time zone so I had to modify output time.

...
    from_unixtime(s.START_DATE / 1000) - interval 1 hour as "Start Date", 
...

Maybe it will help somebody

Errol

Answer 6

Not sure! why JIRA doesn't provide a very simple Rest Endpoint to just spit all sprints info. Why I have to deal with board/boardID to find sprints in that board, why I have to iterate over all sprints.

I'm administrator user and still hitting some of the sprint # gives me, Sprint does not exist.

Anyways, here's a work-around script.

#!/bin/bash

JIRA_URL="http://my_jira_server:8080"

users_sprint_limit_cmd_line_arg="$1"
# First parameter passed to the script is a NUMBER (for how many sprints a user wants to iterate over.
## I know!! it's a work-around for dealing with "Sprint does not exist" and
## becasue there's no shitty direct JIRA Rest API that exist, to query JIRA server, to spit all SPRINTS with info (start/end date) in just one call.    

## You can use API token (or base64 hash). I'm just going rouge here.
user="a_user_user_who_can_read_any_sprint_or_serviceuser_or_admin"
pass="D00M4u!"

## Set build number variable
b_no=${BUILD_NUMBER:="999999"}

## At the end, you'll have a Temp file will store all sprints info, Valid will contain only valid sprints.
temp_sprint_file="/tmp/all_sprints_startdates_${b_no}_temp.txt"
valid_sprint_file="/tmp/all_sprints_startdates_${b_no}.txt"


## Clean files
rm ${temp_sprint_file} ${valid_sprint_file} || true;

## Sprint counter
sprint_no=1

result="ToBeSet"
## Iterate over all sprints and find their start/stop dates.
## -- This is one-odd way to find sprint's start/end dates, but it works!!
## -- A user can pass a larger value in while condition "-lt value" via cmd line 1st param.
while [[ $sprint_no -lt ${users_sprint_limit_cmd_line_arg} ]];
do
    ## assumes 'jq' is installed. --OR run: sudo yum install jq
    ## --------------------------
    result="$(curl -s -u $user:$pass -X GET -H 'Content-Type: application/json' "${JIRA_URL}/rest/agile/1.0/sprint/${sprint_no}" | \
              jq | \
              egrep "name|startDate|endDate" | \
              cut -d'"' -f4 | \
              sed "s/T[0-9][0-9]:[0-9][0-9].*$//" | \
              tr '\012' ',' | \
              sed "s/,$//")";
    echo "${result}" >> ${temp_sprint_file}
    ((sprint_no++));
done

## Find valid sprints which have valid start/end dates.
grep "[A-Za-z],[0-9].*,[0-9]" ${temp_sprint_file} > ${valid_sprint_file};

echo -e "\n\n-- Sprints and Start/End Date file is available here: ${valid_sprint_file}\n\n"

Running cat command on this generated sprint data file will give you, something like:

 1  Trumpy Trump,2019-01-09,2019-01-23
 2  Magical Modi,2019-01-18,2019-02-01

Where, you can add a line in the above script, to use it as a pure CSV file by having a header line i.e. Sprint_Name,Sprint_Start_Date,Sprint_End_Date, I just didn't do that as my use case was to use just this file as a reference file.

A related post regarding dates: BASH: How to find no. of days (considering only "Network / Business Days") between two dates (i.e. exclude weekends Saturday/Sunday)

Purslane