Summary

Jira only allows the edition of a Sprint's dates while it's still open. To edit closed Sprints we need to perform direct DB updates.

Missing or somehow wrong dates may compromise Agile Reports and metrics, requiring a fix.

The Sprint data is stored in the AO_60DB71_SPRINT table and these are the data fields:

• START_DATE:     The Sprint's planned Start Date
• END_DATE:       The Sprint's planned End Date
• ACTIVATED_DATE: The Sprint's actual Start Date (when the "Start Sprint" action was performed)
• COMPLETE_DATE:  The Sprint's actual End Date (when the "Close Sprint" action was performed)
• STARTED:        If the Sprint has been started
• CLOSED:         If the Sprint has been closed

The dates are stored in Epoch format (seconds or milliseconds since 1970-01-01 00:00:00). You may use Epoch converted utilities like epochconverter.com to translate Epoch time to a human readable date and vice-versa.

Environment

Any version of Jira Software Data Center (or Server).

Any version of Jira Service Management Data Center (or Server).

Solution

First, get the ID of the Sprint from which you need to update the dates, by following the instructions from the KB article Jira Software: How to identify the ID of a sprint.

Then, confirm in the database the dates the Sprint has by running the query below ( Replace <SPRINT_ID> with the ID of the Sprint):

select "ID", "NAME", "START_DATE", "END_DATE", "STARTED", "ACTIVATED_DATE", "CLOSED", "COMPLETE_DATE"
from "AO_60DB71_SPRINT"
where "ID" = <SPRINT_ID>;

In this example, the Sprint 3 has been closed but the COMPLETE_DATE is missing:

 ID |      NAME       |  START_DATE   |   END_DATE    | STARTED | ACTIVATED_DATE | CLOSED | COMPLETE_DATE 
----+-----------------+---------------+---------------+---------+----------------+--------+---------------
  3 | Sample Sprint 3 | 1684781460000 | 1685559060000 | t       |  1685137938093 | t      |              

If we're after updating the COMPLETE_DATE or the START_DATE, we may rely on the Sprint audit log for insights into when the specific Sprint was started or ended, by running the query below ( Replace <SPRINT_ID> with the ID of the Sprint):

select * from "AO_60DB71_AUDITENTRY" where "ENTITY_ID" = <SPRINT_ID> order by "ID" asc;

Example of result:

    CATEGORY     |         DATA          | ENTITY_CLASS | ENTITY_ID | ID |     TIME      | USER  
-----------------+-----------------------+--------------+-----------+----+---------------+-------
 SprintOpenClose | {"operation":"OPEN"}  | SPRINT       |         3 |  2 | 1685137938152 | admin
 SprintOpenClose | {"operation":"CLOSE"} | SPRINT       |         3 |  3 | 1685150997718 | admin

On the above example the Sprint #3 was started (OPEN) at 1685137938152 and completed (CLOSE) at 1685150997718. We can use these dates to update the ACTIVATED_DATE and COMPLETE_DATE respectively, if needed. If the audit entries are missing or you're looking to update other dates, you may just convert them to Epoch time and update it with the respective command:

• Replace <SPRINT_ID> with the ID of the Sprint
• Replace the Epoch time dates with the new date values

UPDATE "AO_60DB71_SPRINT" SET "START_DATE" = 1684781460000 WHERE "ID" = <SPRINT_ID>;
UPDATE "AO_60DB71_SPRINT" SET "END_DATE" = 1685559060000 WHERE "ID" = <SPRINT_ID>;
UPDATE "AO_60DB71_SPRINT" SET "ACTIVATED_DATE" = 1685137938152 WHERE "ID" = <SPRINT_ID>;
UPDATE "AO_60DB71_SPRINT" SET "COMPLETE_DATE" = 1685150997718 WHERE "ID" = <SPRINT_ID>;

Lastly, it's advised you restart Jira to have the changes refreshed in the cache. For a multi-node Jira, a rolling-restart would suffice.

Alternatively, you may try creating a Sprint (and deleting it) or changing the Sprint goal of any Sprint to have the caches refreshed — though the restart is the assertive way to have the caches rebuilt.