How to fix resolution dates retroactively in Jira through the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
An admin may face a scenario where a lot of issues are in a state with a defined resolution value (not unresolved) but the resolutiondate is empty. This will cause problem on boards and for reporting and there will be inconsistencies.
Note that there's a way to perform a similar task by transitioning the issue once again and setting the resolution through workflow but you will lose the date when the resolution was initially set:
Environment
9.4.2
Diagnosis
You have issues that have a resolution value but do not have a resolutiondate defined. To find these records, you can run the following JQL on issue navigator:
resolution != Unresolved AND resolutiondate is EMPTYThis query will provide all issues that had a resolution set but the resolutiondate is still null and provides the date when this transition happened for project with key "MYKEY" and the workflow has a single final status (update as needed):
- Remove the AND p.pkey = 'MYKEY' statement IF you'd like to query for all projects.
select distinct (select cg2.created
FROM jiraissue ji2, changeitem ci2
JOIN changegroup cg2 on ci2.groupid=cg2.id
WHERE ci2.field = 'resolution'
AND ci2.oldvalue is null
AND ji2.id=cg2.issueid
AND ji.id=ji2.id order by cg2.created desc limit 1)
,ji.id, p.pkey, ji.issuenum
FROM jiraissue ji, project p, changeitem ci
JOIN changegroup cg on ci.groupid=cg.id
WHERE ci.field = 'resolution'
AND ji.project = p.id AND p.pkey = 'MYKEY'
AND ci.oldvalue is null
AND ji.resolutiondate is null
AND ji.resolution is not null
AND ji.id=cg.issueid;This query will provide all issues that had a resolution set but the resolutiondate is still null and provides the date when this transition happened for project with key "MYKEY" and the workflow has a multiple final statuses (update as needed):
-- Please update the query using the right <PROJECT_KEY> & status you want to fix <STATUS>
SELECT DISTINCT
(SELECT cg2.created
FROM changeitem ci2
JOIN changegroup cg2 ON ci2.groupid = cg2.id
JOIN jiraissue ji2 ON ji2.id = cg2.issueid
WHERE ci2.field = 'resolution'
AND ci2.oldvalue IS NULL
AND ji.id = ji2.id
ORDER BY cg2.created DESC
LIMIT 1) AS last_created,
ji.id,
p.pkey,
ji.issuenum
FROM jiraissue ji
JOIN project p ON ji.project = p.id
JOIN changegroup cg ON ji.id = cg.issueid
JOIN changeitem ci ON ci.groupid = cg.id
JOIN issuestatus js ON ji.issuestatus = js.id -- Joining issuestatus table
WHERE ci.field = 'resolution'
AND p.pkey = '<PROJECT_KEY>'
AND ci.oldvalue IS NULL
AND ji.resolution IS NOT NULL
AND ji.resolutiondate IS NULL
AND js.pname = '<STATUS>';Cause
Misconfigured automation or customized workflows may update just the resolution, bypassing the default post-function that usually sets the value for the date. After fixing the fallout, review for any new occurrences to track the exact root cause for your case.
Solution
These are the queries to be used in the case where a single project will be fixed at a time. On this example, "MYKEY" is the project key to be fixed.
Be sure to run the query for the Diagnosis section beforehand and save the contents. It can be used to validate how many records are impacted and validate the date which will be set.
Queries were tested on PostgreSQL. Some modifications may be needed to accommodate different syntax from other database products.
As always, follow best practices for change management and test all the steps on a staging/test environment first, preferably one that has been recently copied from production.
This update will set the date seen on the previous query for each of the impacted issues when the workflow has a single final status:
update jiraissue ji SET resolutiondate = (select cg2.created
FROM jiraissue ji2, changeitem ci2
JOIN changegroup cg2 on ci2.groupid=cg2.id
WHERE ci2.field = 'resolution'
AND ci2.oldvalue is null
AND ji2.id=cg2.issueid
AND ji.id=ji2.id order by cg2.created desc limit 1)
FROM project p, changeitem ci
JOIN changegroup cg on ci.groupid=cg.id
WHERE ci.field = 'resolution'
AND ji.project = p.id AND p.pkey = 'MYKEY'
AND ci.oldvalue is null
AND ji.resolutiondate is null
AND ji.resolution is not null
AND ji.id=cg.issueid;This update will set the date seen on the previous query for each of the impacted issues when the workflow has a multiple final statuses and you want to update specific ones:
WITH issue_data AS (
SELECT DISTINCT
(SELECT cg2.created
FROM changeitem ci2
JOIN changegroup cg2 ON ci2.groupid = cg2.id
JOIN jiraissue ji2 ON ji2.id = cg2.issueid
WHERE ci2.field = 'resolution'
AND ci2.oldvalue IS NULL
AND ji.id = ji2.id
ORDER BY cg2.created DESC
LIMIT 1) AS last_created,
ji.id
FROM jiraissue ji
JOIN project p ON ji.project = p.id
JOIN changegroup cg ON ji.id = cg.issueid
JOIN changeitem ci ON ci.groupid = cg.id
JOIN issuestatus js ON ji.issuestatus = js.id
WHERE ci.field = 'resolution'
AND p.pkey = '<PROJECT_KEY>'
AND ci.oldvalue IS NULL
AND ji.resolution IS NOT NULL
AND ji.resolutiondate IS NULL
AND js.pname = '<STATUS>'
)
UPDATE jiraissue ji3
SET resolutiondate = issue_data.last_created
FROM issue_data
WHERE ji3.id = issue_data.id;A project re-index is required for the change to take effect and for JQLs and reports to take the new value into account. If multiple projects were updated, consider running a full re-index instead.