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

[Nicolo Chioato]

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

[Brad as Paul · Accepted solution]

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]

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

[Bill Miller]

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]

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]

@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.

[Daniel Brvnišťan]

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

[Muhammad Bilal]

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]

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

[Muhammad Bilal]

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.

[Daniel Brvnišťan]

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]

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]

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]

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

[Dario B]

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

[Muhammad Bilal]

Hi @Dario B 

Please see the screenshot of the dropdown fields i have.

[Muhammad Bilal]

[Dario B]

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

[Muhammad Bilal]

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]

Hi @Dario B 

Thank you so much this case is solved now :)

Regards

Muhammad Bilal

[Dario B]

Nice one @Muhammad Bilal ! :) 

I am happy to know I was able to help.

Have a nice weekend,
Dario

[Edgardo Cabezas]

Thanks! great help! 

Regards

Edgardo

[hugo_campbell]

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:

For the rest I am exactly following your instructions.

[Dario B]

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:

• https://marketplace.atlassian.com/apps/1218809/quantify-timespent?hosting=cloud&tab=support

Cheers,
Dario

[hugo_campbell]

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.

[Dario B]

You are very welcome @hugo_campbell .

Enjoy the weekend! :) 

[Deleted user]

Nice. TY!

[Germán Isaurralde]

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]

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.

[Yogeesha Adiga]

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!

[Nic Brough -Adaptavist- · Accepted solution]

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_]

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

[George Mihailoff]

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"

[David Keaveny]

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]

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

[Dario B]

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

[Christopher Emerson]

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-]

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

[Dakotah North]

Wow ... that is a very unhelpful response. 

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

[Camille]

Pretty bad response from a community champion.

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

[Dario B]

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

[Camille]

@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]

@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;

[Dario B]

See also:

• https://community.atlassian.com/t5/Jira-questions/How-can-I-change-the-Resolved-Date-for-an-Issue/qaq-p/253663
• https://community.atlassian.com/t5/Questions/how-does-the-resolution-date-and-time-get-set-We-have-conditions/qaq-p/268945

[Camille]

@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]

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.

[AP]

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}}

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

[Daniel Brvnišťan]

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]

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]

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

[Chris]

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]

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:

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

Cheers,
Dario 

[Ameya]

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.

[lnelson1]

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

[Bill Tanner]

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 :) )

[Daniel Soares]

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

[TomH]

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

Thanks all