Is there a way to change the "Resolution Date" in Jira?

Nicolo Chioato
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!
May 16, 2012

Sometimes an issue isn't resolved in the moment of the pressure of the "Resolve Issue" button, but it could be a user resolves an issue in the day T and he presses the "Resolve Issue" button after 2 days. The Resolution Date showed is T+2, but I'd like to register T.

Is there a way to obtain T or must I create a custom field?

Thank You

10 answers

2 accepted

19 votes
Answer accepted
Brad as Paul
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!
April 2, 2018

In JIRA Cloud it is possible to update the resolution date by doing a CSV import.

Just export a CSV of the issues you need to update. Add a "resolved date" column, populate dates, then import into JIRA as Admin via External System Import.

Make sure to set the date formatting to match in CSV and import tool. Also include the issue-ID and summary in tour CSV. If the issues are also missing a "resolution" you will also need to update the "resolution" in the same import, otherwise the "resolved date" will not change.

Viacheslav Starovoytov June 14, 2018

Thanks Paul! Your example has just helped me change resolved dates in the cloud! It is like a magic )

Bill Miller June 25, 2018

Thanks for this, I may try it out.  I had on premises self-hosted at my last customer, but we're using Cloud here.  Yay!!  Really, thank you @Brad as Paul !!

Francois Toubol
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!
July 10, 2018

It looks like it is Issue Key (as opposed to Issue ID), that needs to be used to map. When you do so in the wizard a message appears at the top saying "You have selected Issue Key mapping. Please note that importer will update existing issues matching the Issue Key from the CSV file."

The problem is: it does not work for "Date Resolved" for me...

Slava Starovoitov July 12, 2018

@Francois Toubol CSV file shuld contain four columns: they are Issue Key, Summary, Resolution and Date Resolved.

You should map Issue Key, must map Summary, and then choice the Resolution and the Date Resolved opposite other fields. Also you should type correct Date format on the previous step.

Like # people like this
Daniel Brvnišťan November 16, 2019

It is also possible to use export/import in self-hosted instances:

  • search for the issues you want to edit
  • export to CSV (it's enough to export Issue key, Summary, Resolution and Resolution date fields)
  • in Excel fill the Resolution and Resolved date
  • export to CSV UTF-8
  • import using External system import (CSV) - map issue key (so it updates the correct issues) as well as Summary (mandatory) and your filled Resolution and Resolved date fields
Like # people like this
Muhammad Bilal January 16, 2020

Hi @Daniel Brvnišťan 

I'm trying to add issues via import button, I just want to change the resolution time of the ticket (resolved) and nothing else, the page map issue key I don't think I can understand, do you have steps that I just have to change the fixed date that it doesn't change for me the fixed date that creates a couple of tickets in the same project.

 

Thank you

Muhammad Bilal

Daniel Brvnišťan January 16, 2020

Hi Muhammad,

  1. Do you have all these columns in your CSV?
    Issue key, Summary, Resolution and Resolution date
    You need to have all 4 if you want to update Resolution date of existing tickets.
  2. When you are on the mapping screen, do you see those four fields on the left, and can you select the respective system fields on the right?

Thansk,
Daniel

Like # people like this
Muhammad Bilal January 16, 2020

Hi @Daniel Brvnišťan 

1) Yes, i have all the four columns in my CSV.

2) On the map fields i also have these four CSV fields listed, but i think i am not understanding here what is the Jira field please see the image attached. For example for the issue key i have to select which field? for the resolution date which field? i have tested once and it created a new 5 tickets on the same project rather than changing the old ticket resolution time. May be i am doing something wrong.

 

1.JPG2.JPG

Like Germán Isaurralde likes this
Daniel Brvnišťan January 16, 2020

You need to select the same field - so in the first drop down, find "Issue key", in the second, find "Resolution" etc.

The reason it creates new tickets is because the Issue Key is not mapped - the import does not know that the lines in your CSV correspond to existing tickets and which ones.

Muhammad Bilal January 16, 2020

Hmm and why i can not see the same JIRA FIELD in the drop down box? for issue key it should show me in the JIRA FIELD ..issue key and this is the same case for all CSV FIELDS.

Daniel Brvnišťan January 16, 2020

That I don't know unfortunately... I see it there. Are you using Server edition, right? Do you have Admin / System Admin rights?

Muhammad Bilal January 16, 2020

I am the Jira administrator and i am using Jira Cloud. 

Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 16, 2020

@Muhammad Bilal can you share a screenshot of what you have in the drop-down?

Muhammad Bilal January 16, 2020

Hi @Dario B 

 

Please see the screenshot of the dropdown fields i have.

 

1.jpg

Like Edgardo Cabezas likes this
Muhammad Bilal January 16, 2020

22.jpg

Like # people like this
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 16, 2020

Thanks for the screenshot @Muhammad Bilal !

So, the problem here is that you are importing issues from CSV from the search page, as explained in: Creating issues using the CSV importer

  1. From the Jira Cloud application header, click Issues > Search for issues.
  2. Click more ( ••• ) > Import issues from CSV
  3. ...

 

While you should use the CSV import from External System Import in the administration section of your Jira instance as documented in: Importing data from CSV

  1. Choose the Jira icon (, or ) > Jira settings > System.
  2. In the Import and Export section, click External System Import and then click CSV.
  3. ...

 

The first functionality can be used to bulk create issues from CSV into an existing project also by non-admin users that have create issue permission in the destination project and it is therefore limited compared to the other one.

I hope this explains.

 

Cheers,
Dario

Like # people like this
Muhammad Bilal January 16, 2020

Hi @Dario B 

 

Thank you so much, i have successfully used the CSV import and it shows me the exact map fields. I am still having error while changing the date. Please confirm if the date format is correct. in my import file i am using like this: 16-12-2019 11:20:00 AM

2020-01-17 07:43:46,605 INFO - Import started by muhammad.bilal using com.atlassian.jira.plugins.importer.imports.csv.CsvDataBean
2020-01-17 07:43:46,750 WARN - Unable to parse datetime: 16-12-19 11:20



Muhammad Bilal January 17, 2020

Hi @Dario B 

 

Thank you so much this case is solved now :)

 

Regards

Muhammad Bilal

Like Dario B likes this
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 17, 2020

Nice one @Muhammad Bilal ! :) 

I am happy to know I was able to help.

 

Have a nice weekend,
Dario

Edgardo Cabezas
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!
March 10, 2020

Thanks! great help! 

Regards

Edgardo

Like # people like this
hugo_campbell January 27, 2021

Hello @Dario B, I'm almost there. However, The issues are not created nor updated:

2021-01-27 19:06:58,514 INFO - ------------------------------
2021-01-27 19:06:58,514 INFO - Importing: Issues
2021-01-27 19:06:58,514 INFO - ------------------------------
2021-01-27 19:06:58,514 INFO - Only new items will be imported
2021-01-27 19:06:58,631 INFO - Importing issue: [externalId='autoid-3702845111255600032', summary='XXXX']
2021-01-27 19:06:58,713 INFO - 0 out of 1 issues successfully created
2021-01-27 19:06:58,717 INFO - ------------------------------
2021-01-27 19:06:58,717 INFO - Finished Importing : Issues
2021-01-27 19:06:58,717 INFO - ------------------------------


Do you know what might be happening?

This is what I have in my csv file:

Untitled 3.png

For the rest I am exactly following your instructions.

Like Yogeesha Adiga likes this
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 28, 2021

Hello @hugo_campbell ,

Can you confirm you are using External System Import and provide a screenshot of how you configured the time format? 

Also, can you confirm the resolution date has not been actually modified, since I don't see any error in the logs, it just says it did not create any issue (but it does not say it did not modify the given issue)?

 

Finally, if nothing works, you can try the free app mentioned by @George Mihailoff in his comment:

 

 

Cheers,
Dario

Like hugo_campbell likes this
hugo_campbell January 28, 2021

Hello @Dario B,

After retrying by checking the mapping box for Key Issue it worked, the existing tickets are updated.

 

Thank you very much!

H.

Like Dario B likes this
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 29, 2021

You are very welcome @hugo_campbell .

Enjoy the weekend! :) 

Deleted user October 19, 2021

Nice. TY!

Germán Isaurralde February 28, 2023

Late but... if someone is reading this... 

I have left as null one summary and It works... 

you have to do everything is said in this thread but also to leave null one of the summary (or two :))

Tobias Nendel
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!
April 1, 2023

Here is a ScriptRunner script that helps you generating the needed csv:

// Define a JQL query to search for the issues on which you want to update the resolution
def query = 'resolutiondate IS EMPTY and status = Done and projectType = "software" order by created DESC'

// The Name of the resolution to be set
def resolutionName = "Done"

def searchReq2 = get("/rest/api/2/search")
.queryString("jql", query)
.queryString("maxResults", "1")
.asObject(Map)
assert searchReq2.status == 200

int searchResultTotal = (int) searchReq2.body.total
int loopRunsCounter = searchResultTotal.intdiv(50)

int startAt = 0
String output = '"Project Key","Project Name","Project Type","Issue Key","Resolution","Date Resolved","summary"\n'

for (int i = 0; i <= loopRunsCounter; i++){
def searchReq = get("/rest/api/2/search")
.queryString("jql", query)
.queryString("expand", "changelog")
.queryString("expand", "projects")
.queryString("fields", "summary")
.queryString("fields", "project")
.queryString("maxResults", "50")
.queryString("startAt", startAt)
.asObject(Map)
startAt += 50
Map searchResult = searchReq.body


searchResult.issues.each { Map issue ->
resolutiondate = null
issue.changelog.histories.find { LinkedHashMap history ->
history.items.find { LinkedHashMap item ->
if(item.fieldId == "status" && (item.toString == "Done" || item.toString == "finished" ) ){
resolutiondate = history.created
return true
}
}
}
output += '"'
output += [
issue.fields.project.key,
issue.fields.project.name,
issue.fields.project.projectTypeKey,
issue.key,
resolutionName,
resolutiondate,
issue.fields.summary
].collect{ field ->
field?.replaceAll('"','""')
}.join('","')
output += '"\n'
}
}

logger.info("\n\n\n\n\nCopy Paste below into a new csv and use JIRA import wizard under {baseURL}/secure/admin/ExternalImport1.jspa to import.\n----------------")
logger.info(output)

 Check your logging output.

Like Adrian Castillo likes this
Yogeesha Adiga
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!
September 9, 2023

When exported, CSV also included Issue id and for some reason this was blocking update to Resolved field. Keeping only 4 fields did work finally for me. Thanks for this post!

0 votes
Answer accepted
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.
May 16, 2012

No, the resolution date really is "the last time the resolution was set", it's not actually physical data, it's derived from the time the resolution changed.

As you suggest, you'll need a custom field.

Kyle Moseley _blueridge_cx_ November 20, 2014

Do you know of any ways to do this via scriptrunner?

2 votes
George Mihailoff July 14, 2020

Surprisingly, this is a real issue. We decided to do something about it and released a resolution checker as part of our free addon (Quantify).

1. You can use JQL-powered search to lookup issues

2. Because we deal with things like time in status, throughput and other Kanban metrics we already know what resolution dates to suggest

3. Then selected items are available in the correct CSV format to use in the "External system import --> CSV"

Quantify Resolution Checker.png

2 votes
David Keaveny July 24, 2013

If you were running the self-hosted version, and you had reliable backups, and you took the service offline first, you could edit the SQL in the underlying database, in which case it's the jiraissue.RESOLUTIONDATE field, but for OnDemand you're probably on your own.

Mario Alfaro November 10, 2020

Thanks a lot, it still works. To import the CSV file you need to define at least 4 columns:

Issue Key, Resolution, Resolved Date, Summary

Like Dario B likes this
1 vote
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 20, 2018

Thanks a lot everybody, I have verified that it is indeed possible to modify the "Resolved" field with CSV import and I have updated the below bug ticket for Jira Cloud:

https://jira.atlassian.com/browse/JRACLOUD-65502

1 vote
Christopher Emerson March 28, 2018

Very sad to find this out. We've had tickets on Cloud that accidentally don't get marked resolved and there is not even an admin-only way to fix them to show when they were actually resolved?! Wow. Embarrassingly bad design folks.

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.
March 28, 2018

Er, you're expected to not badly design your workflow, not botch fixes in later.

Dakotah North
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!
May 11, 2018

Wow ... that is a very unhelpful response. 

Thats like saying that you’re expected to never make a mistake.

Like # people like this
Camille January 22, 2019

Pretty bad response from a community champion.

Modifying the resolved date is absolutely a feature that should be possible.

Like # people like this
Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 23, 2019

@Camille As you can read below this is possible in Jira Cloud using CSV import.

Camille January 23, 2019

@Dario B we are using Jira Server. And having to import a CSV is a really unintuitive, time-consuming workflow as opposed to just editing a field as per usual in Jira. I realise this might not be possible now but I am just putting my feedback in for this feature request.

Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 31, 2019

@Camille apologies for late reply somehow I missed this.

Then, for Jira server you can just do that from the DB. Something 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;
Camille January 31, 2019

@Dario B appreciate the reply, but having to modify the DB each time you want to change a date is very unproductive. We also would need to ask the Jira Administrator to do this as a lot of us don't have access to this. I just want the resolution date to be editable just like due date. This is not a question of how to do this, as it's clear it's not possible to do it easily, but rather a feature request.

Daniel Brvnišťan November 16, 2019

I would say this is by design.

Enabling editing the date at whim (even by admin) just encourages users to change data later as opposed to implementing both technical (correct workflow) and procedural (set your damn task to done when you actually do it) ways to have the data correct.

If you are an admin, make sure the "check the resolutions are set right" is in your definition of done when creating/editing workflows.

I am not not saying I have never done the mistake - that's why I am here :D But I would not be happy if me and other admins could just edit any issue's resolved date anytime easily.

Like # people like this
0 votes
AP January 10, 2024

The challenge here is when the tickets are tied to a Workflow which doesn't allow you to transition the status out and back to some configured 'Completed' status.  It's super convenient when searching for outstanding tickets using JQL to find all of those without an resolution (ie 'resolution = Unresolved', or conversely 'resolution != Unresolved' for those that are completed). So how to fix?

The easiest thing to do (only done this in cloud) to fix this is via a Jira automation, and should only take a minute:

  1. Create a new Jira automation
  2. Set the trigger to be manual and accept user input with a dropdown question
    1. Optionally limit use to the desired user group(s)
  3. Include the desired resolution system names in the options.
  4. Add an 'Edit Issue' action and choose 'Resolution (System)' from the list.
  5. Type the smart value name of user input field as the value as opposed to choosing one of the options. example:
    1. {{userInputs.resolution}}

Screenshot 2024-01-10 at 5.24.58 PM.pngScreenshot 2024-01-10 at 5.24.13 PM.png

Now when you find these tickets you can just run this automation!

0 votes
Daniel Brvnišťan November 16, 2019

It is possible to use export/import in self-hosted instances as well:

  • search for the issues you want to edit
  • export to CSV (it's enough to export Issue key, Summary, Resolution and Resolution date fields)
  • use Excel to fill the Resolution and Resolved date
  • export to CSV UTF-8
  • import using External system import (CSV) - map Issue key (so it updates the correct issues) as well as Summary (mandatory) and your filled Resolution and Resolved date fields
Bill Tanner December 4, 2019

We couldn't fill the Resolution date field directly via CSV import.  In my post above, I provided steps to do it similar to those here, but we had to first create a custom field, fill it with the resolution date, and then copy that value over to the Resolved field.

Daniel Brvnišťan January 16, 2020

Interesting, I had those fields/columns in the CSV, and could select the system field in the mapping screen, no problem.

Chris July 29, 2020

I can export my issue only as an XML or word. How can I import that to External System Import? It gives 3  options CSV, JSON and None. I chose None and tried to import word file. It is asking me for the delimiter of CSV file . 

Dario B
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 14, 2020

Hi @Chris ,

Please notice you added a reply to a thread from 2012 and this is usually not advised. 

I am not sure whether you are using Jira Cloud or Server, however, the way to export to CSV is showed in below screenshot:

Export-to-CSV.jpg

 

I hope this helps. If not, please create a new thread for your issue.

 

Cheers,
Dario 

0 votes
Ameya August 25, 2019

This issue happened with me in a critical project when i had bulk changed the Resolution using script runner built-in functionality. The solution is for Jira DC and PostgreSQL. Please try the same on Lower environment before doing it Live. It works.

The steps are:

  1. Spin up a copy of the Jira database backup from the latest version before the issue occured
  2. Export the id and resolutiondate values to a CSV file:
    \copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/output-dev.csv' with csv; 
  1. Manually add a header of id and resolutiondate to the CSV.
  2. Back up the potentially affected issues in the jiraissue table:
    \copy id, resolutiondate FROM jiraissue WHERE project = "PROJECT_ID" AND resolutiondate IS NOT NULL) to '/tmp/prod-backup.csv' with csv; 
  1. Create a temporary table in Jira DB to house the updated value:
    CREATE TABLE tmp_issues (id numeric(18,0), resolutiondate timestamptz); 
  1. Import issues from the CSV generated in step 2 to the table we just created:
    \copy tmp_issues(id, resolutiondate) FROM '/path/to/output-dev.csv' DELIMITER ',' CSV HEADER; 
  1. Confirm this data looks correct:
    SELECT * FROM tmp_issues limit 50;
    <two columns with id and resolutiondate>
    \d tmp_issues
                   Table "public.tmp_issues"
         Column     |           Type           | Modifiers 
    ----------------+--------------------------+----------- id             | numeric(18,0)            | 
     resolutiondate | timestamp with time zone | 
  1. Update the issues using our temp table:
    UPDATE jiraissue SET resolutiondate = tmp_issues.resolutiondate FROM tmp_issues WHERE jiraissue.id = tmp_issues.id; 
  1. Once this finishes, perform a rolling restart of Jira.
  2. Drop temporary table:
    DROP TABLE tmp_issues; 
  1. Clean up jiraissue table:
    VACUUM jiraissue; 
  1. Perform a project reindex of Affected project.
0 votes
lnelson1 June 25, 2019

I'm going to go ahead and give it a try, but can anyone confirm if this will work for JIRA Server?

Bill Tanner December 4, 2019

There is a way to do it in Jira Server.  I know because I just did it, and it took a long time to figure out a viable solution.  It is a little tedious, but you don't have to touch the backend database.

 

1) Create a new custom field as a placeholder for the resolution date.  We used a Date Time Picker field.

2) In your CSV file, include a column with the resolution date for each issue you are updating.  I set the format to "yyyy-MM-dd HH:mm:ss" (using the custom format function in Excel).  I have found Jira to be very sensitive with the date format when doing CSV imports.

3) Via the External System Import function, import the CSV file. Be sure to set the date format to "yyyy-MM-dd HH:mm:ss".  I match the Key and Summary, and match the Resolution Date in your CSV file to the new custom field you created in Step 1.  Check the "Map Field Value" for the Resolution Date field.

4) Confirm the CSV import worked and your new field is populated

5) In your work flow, create a new recursive transition.  Since these are all resolved issues, it should be from "Done" to "Done" or something along those lines.

6) On the new transition, add a post function where you copy the value from your new custom field created in Step 1 to the "Resolved" field. (Resolved is the system field that captures the data of resolution).  We used the JSU add-on, and the post function "Copy Value From Other Field (JSU).

7) Do a bulk update, selecting all the issues you want to update the Resolved date.  Execute the new transition you created in Step 6.

 

If you have any questions, feel free to reach out.  This was a huge pain to figure out, but the solution is relatively simple (assuming you get the date format correct :) )

Like # people like this
Daniel Soares February 16, 2022

This was the final solution for me Thank you @Bill Tanner 

TomH August 20, 2023

Amazing stuff!
I used the solution from this thread and it solved all my problems 

Thanks all

ArulAnthonySebastian Nadar May 24, 2024

Without JSU any other workaround?

Like Dave Liao likes this

Suggest an answer

Log in or Sign up to answer