How can I change the Resolved Date for an Issue?

Jim Randell June 15, 2012

I have several issues which should have been resolved some time back. I want to close and resolve them now, but do not know how to properly backdate the Resolved date. it is important for reporting purpuses and GH charts.

Thanks,

-Jim

5 answers

3 votes
Chris Knoche May 27, 2013

I know I'm hopping onto an old thread, but this was the first page I came across when searching for an answer.

This turned out to be a very straight forward fix in SQL. The resolved date is stored in the dbo.jiraissue table.

I updated the resolved date on mine by executing the following:

UPDATE dbo.jiraissue
SET UPDATED = '2013-05-23 12:19:22.087', RESOLUTIONDATE = '2013-05-23 12:19:22.127'
WHERE pkey = 'CCSP-455'

Just change the pkey to the issue key that you're trying to update. The update didn't require any re-indexing nor did I bring Jira offline (rebel!).

Hope this helps anyone doing the same search as me.

UPDATE FOR JIRA 6.1+:

It looks like they deprecated the pkey identifier starting in Jira v6.1. This is the new query I ran to accomplish the same in the new version:

UPDATE dbo.jiraissue
SET UPDATED = '2014-02-03 14:07:22.087', RESOLUTIONDATE = '2014-02-03 14:07:22.087'
WHERE PROJECT = 10104 AND issuenum = 44

It may take some searching for you to identify the PROJECT value.

David Keaveny July 24, 2013

Works for me; to be safe, I did a complete reindex afterwards. I was actually fixing up resolve dates on issues which were imported from another system, so I was able to do a direct join between the two SQL Server databases and update all the JIRA issues in one go :-)

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 18, 2014

Remember that this is only appropriate for imported issues that don't have a history entry for when the resolution changed from <null> to <something>

If you have history records, then hacking the resolution date will make your data inconsistent nonsense.

Like # people like this
Peter Bengov
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 3, 2015

Thanks for the update here, knoche. Very helpful

Praecipio Consulting
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
January 24, 2017

This approach doesn't work on JIRA 7, changed both values and the Resolved Date didn't change, I did a locked re-index and tried with different issues with no luck, is there anywhere else i can get to that value?

2 votes
Adrian Vital
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 20, 2020

Use ScriptRunner - this will update only 1 ticket at  a time. 

Paste this template the ScriptRunner script console,

Update the ticket number and the date.  Run.    

 

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.MutableIssue
import java.sql.Timestamp
import com.atlassian.jira.util.ImportUtils
import java.text.SimpleDateFormat
import java.text.DateFormat;
import java.util.Date;

DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");

Date date = dateFormat.parse("05/23/2019 06:00:00");

long time = date.getTime();

Timestamp targetDate = new Timestamp(time);

IssueManager issueManager = ComponentAccessor.getIssueManager()

MutableIssue missue = (MutableIssue) issueManager.getIssueObject("PS-2469")

missue.setResolutionDate(targetDate);

missue.store();

return (targetDate);

Christoph Beer May 24, 2020

works fine for me.

But history entry mismatch needs to be taken into account.

Thanks!

Yves Martin
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 1, 2020

It is important to trigger issue reindex then !

import com.atlassian.jira.issue.index.IssueIndexingService


def indexingService = ComponentAccessor.getOSGiComponentInstanceOfType(IssueIndexingService)
boolean wasIndexing = ImportUtils.isIndexIssues()
ImportUtils.setIndexIssues(true)
indexingService.reIndex(missue)
ImportUtils.setIndexIssues(wasIndexing)
PeteToscano August 1, 2020

Would it be possible to loop this over the issues in a saved filter or JQL?

PeteToscano August 1, 2020

Ugh, answering my own question, but for those who follow... https://library.adaptavist.com/entity/perform-a-jql-search-in-scriptrunner-for-jira was very helpful.

Michiel Schuijer April 13, 2021

I almost started running the script but the warning put me off, showing in the script console:

"(!) Since version v5.0. DO NOT USE THIS as it overwrites all the fields of the issue which can result in difficult to reproduce bugs. Prefer to use QueryDslAccessor to change only needed fields @ line 24, column 8"

scriptrunner-update-issue-resolvedate.png

We're using Jira D.C. 8.14.x with ScriptRunner Version: 6.21.0

I could not create a script myself using QueryDslAccessor as this seems to be possible only from database queries, not ScriptRunner, but I may be wrong.

Does anyone have a workaround or update to this script that uses a method that updates only the Resolution Date field and will not overwrites all fields as the warning implies?

2 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 15, 2012

You can't. The resolved date is derived from the last time you resolved the issue, which you can't alter.

Of course, there's always hacking - in this case, you'd need to use SQL to change the date on the changegroup (and I think you'd need to alter the os_workflow too). That does require backups, downtime and re-indexing though (never alter a Jira database while it's running. Just. No. Never), and will destroy the information about when your users actually resolved it.

1 vote
Thomas Rohrer December 6, 2018

For newer Jira instance SQL command should look like:

update jiraissue set updated = '2018-11-28 09:22:22.498+01', resolutiondate = '2018-11-28 09:22:22.498+01' where id = 59918;
0 votes
Derrick Taylor David June 18, 2012

I was thinking you could go in to the workflow and do the modification that way. Create a new field put it on the transistion. Then as a part of the post workflow actions, have the Resolved date copy the date of the field you just entered.

(Added in later) But make the transistion go back to the same step it was previously ie. New - New, Open -Open, etc> You will lose the last updated date but at least you get your Resolved data back

Also then to protect yourself, go back and make sure that you or the jira-admins are the only people who can see that workflow option.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 18, 2012

No. Generally, that is a very good approach and exactly what I'd do. Looped transitions are immensely useful to get around the weakness in "edit" permissions. It's just that for this one field, it's not right - because it's derived field, you don't need to touch it - and in fact, you can't. It'll take its value from the changed change history.

Suggest an answer

Log in or Sign up to answer