How to update the dates of a closed Sprint in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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 DateEND_DATE:The Sprint's planned End DateACTIVATED_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 startedCLOSED: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
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
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):
1
2
3
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:
Sample output
1
2
3
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):
1
select * from "AO_60DB71_AUDITENTRY" where "ENTITY_ID" = <SPRINT_ID> order by "ID" asc;Example of result:
Sample output
1
2
3
4
CATEGORY | DATA | ENTITY_CLASS | ENTITY_ID | ID | TIME | USER
-----------------+-----------------------+--------------+-----------+----+---------------+-------
SprintOpenClose | {"operation":"OPEN"} | SPRINT | 3 | 2 | 1685137938152 | admin
SprintOpenClose | {"operation":"CLOSE"} | SPRINT | 3 | 3 | 1685150997718 | adminOn 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
1
2
3
4
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.
Was this helpful?