It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

9 answers

1 accepted

0 votes
Answer accepted

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.

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

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.

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

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 @Paul_Iverson !!

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

@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 timothy likes this

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

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

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 Dario B likes this

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

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.

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.

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

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

Dario B Atlassian Team Jan 16, 2020

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

Hi @Dario B 

 

Please see the screenshot of the dropdown fields i have.

 

1.jpg

Like Edgardo Cabezas likes this
Like Edgardo Cabezas likes this
Dario B Atlassian Team Jan 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

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



Hi @Dario B 

 

Thank you so much this case is solved now :)

 

Regards

Muhammad Bilal

Like Dario B likes this
Dario B Atlassian Team Jan 17, 2020

Nice one @Muhammad Bilal ! :) 

I am happy to know I was able to help.

 

Have a nice weekend,
Dario

Thanks! great help! 

Regards

Edgardo

Like Dario B likes this

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.

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.

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

Wow ... that is a very unhelpful response. 

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

Like # people like this

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 Jan 23, 2019

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

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

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

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 Dario B likes this

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

0 votes
Dario B Atlassian Team Jul 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

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

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 Dario B likes this

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.

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

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.

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

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you