ScriptRunner convert convert java.sql.Timestamp to java.util.Date

[April]

Hi there,

I'm trying to create a scripted field to display the fixversion release date on a JIRA issue, and I'd like these to be searchable by date range. I have:

import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.bc.issue.IssueService 
import java.lang.Exception
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.project.version.Version
import java.text.SimpleDateFormat
import java.sql.Timestamp
import java.text.DateFormat
import java.util.Date
Collection versions = issue.getFixVersions()
if (versions==null || versions.size()!=1)
    return null
def version = versions.iterator().next()
return version.getReleaseDate()

But I get:

"The indexer for this field expects a java.util.Date but the script returned a java.sql.Timestamp."

When I try to add .format('yyyy/MM/dd HH:mm'), I get a different error that this method is not valid on a java.util.Date.

ARG!

Anyone have a suggestion?

Thanks,

April

[JamieA · Accepted solution]

Last line should be:

new Date(version.releaseDate.time)

[April]

Thanks! That definitely gets rid of the error.

Is there any way to get a more Jira-normal format like "14/Mar/16"?

Right now it has a kinda fugly "Thu Mar 17 00:00:00 EDT 2016" 

Thanks,

April

[JamieA]

Answers just killed my comment. It said to use the Date template and not the Text template.

[April]

Hi again,

Yes, Answers wasn't letting me type anything in any of three browsers. Seems better now.

I must say, I am astounded by how responsive and helpful you are!

I have asked questions of other vendors before, but never received any responses. I totally thought "Atlassian Answers" was a euphemism for "Unsupported."

Not only do you respond, but you respond quickly and have been SUPER helpful.

For anyone else who ever reads this Answer: ScriptRunner is the BEST! Stop thinking and get out your checkbook

So here's the thing... FixVersions can have multiple entries, so the scripted field should be able to list multiple dates.

I don't know much about Groovy, but if I'm not totally crazy, then a collection is an array, and I should be able to print a result for each $version in versions.

Sadly I have not yet figured out how to do that. When I add a second FixVersion, it kills the field altogether. I can't seem to stumble upon the correct syntax for foreach.

I also don't know if there is such a thing as a multiple date-picker in Jira, or if I should try to make a custom template to display the field with. It would be great to get multiple dates, but I think the users will take whatever they can get:)

Thanks,

April

[JamieA]

Hi April.... thanks for that, much appreciated. Just to clarify, we prefer how-to questions here where the answers may also help others in the future, and bugs etc on the support channel. If you don't know which one it is, then either is fine (but preferably not both  )

We can help you improve the script... you have written it to not display anything if there is more than 1 fix version. But how do you want it to look if there are multiple fix versions? And would it actually be useful if it shows something like: "1st Jan 16, 3rd Jan 16"? 

It could show the version names and release dates, but I'm not sure how you could usefully search on it then, or if users would know to.

The most useful thing, imho, is for it to show the release date of the closest fixVersion. 

Anyway, give us an example of how you want the field to look, and if searching is important, then we'll get back to you tomorrow.

[April]

Hi there,

I had originally pictured something like "14/Mar/16,21/Mar/16" in a happy little list. The other JIRA issue dates appear in that DD/Mon/YY format.

I would assume that most users want to search for a date range, sorta like:

Project = KEY and "Release Date" > 2016-03-01 and and "Release Date" < 2016-03-31

I think the users also really liked seeing the field grouped with the other dates on the issue form, so I hope to achieve that if the field template can support multi-value.

I suppose most recent release date makes sense if only a single value can be returned.

To my knowledge, nobody cares about the timestamp, so it can stay or go whichever is easiest.

If all else fails, then I guess text is the plan, and at least they could get the dates in to appear in their Confluence macros as text values.

Does that answer your questions?

Thanks,

April

[JamieA]

> I had originally pictured something like "14/Mar/16,21/Mar/16" in a happy little list.

Hrm... there is no multi-date field in JIRA that I'm aware of. You could display it as text but then it would not appear in the Dates section of jira, and you would not be able to search for it.

For closest release, there is an example here https://scriptrunner.adaptavist.com/latest/jira/testing/testing-script-fields.html. Personally I think that makes most sense from a usability perspective.

[April]

Hi Jamie,

I checked that out, but I end up back to the "java.util.Date but the script returned a java.sql.Timestamp" problem, and nothing I do converts the date correctly.

It appears that sql.timestamp is a subset of util.date, so perhaps that's why it is so confusing.

Can you show me how to format the ouptut?

Thanks again,

April

[JamieA]

Sorry, after all this I think this is a bug in some newly added code to sanity check the returned object for the indexer. As you say a Timestamp is-a Date, so it should not be complaining. 

You see the error when you press Preview, but you can still Update it and it will work fine.

Alternatively you can change the return link to:

return new Date(releaseDates.first().time)

and the error should go away, but you should not need to do that.

The issue is https://jamieechlin.atlassian.net/browse/GRV-921.

[April]

Oh my goodness, yes, it is only the Previewer that hates everything!

You know, I never learned Java and have only started learning Groovy (yup, that probably is a right-backwards way to do it ), so if the console says I did a bad thing, I just assume it must be true. Doh!

Thanks again for all your help!

[JamieA]

That's fair enough to be honest... I don't really want people to ignore the messages that are supposed to be there to stop them screwing up! It's just that we got it wrong in this case...

You're welcome.

[Mark McCormack (Adaptavist)]

Hi April,

I noticed your comment about ScriptRunner being the best ....

I'm Product Manager for ScriptRunner and wanted to say thanks! Jamie and the whole ScriptRunner team try to provide the best possible support we can so it really helps morale when we get comments like this.

I also wanted to ask if you'd mind helping us by taking a little of what you said and post it into a review in the Atlassian Marketplace? I especially like the comment "Not only do you respond, but you respond quickly and have been SUPER helpful".

It all helps, thanks again.

Mark.

[April]

Hi Mark!

I actually had a brief review posted there, but I've added a new and much lengthier one now, to more fully extoll ScriptRunner's greatness.

Jamie, I just wanted to tell you something...

I have JIRA 6.4.11 in both staging and prod right now, BUT staging has Java 8u73, while prod has Java 8u60. The release date field works fine in staging, but only shows "invalid date" in prod.

I'll be updating prod's Java over the weekend, but thought you would like to know, as the bug you mentioned may not be your own

Thanks guys,

April

[Mark McCormack (Adaptavist)]

Hi April,

I just saw the review you posted. That might just be the best review we've ever had posted! Thanks very much! We appreciate your support.

[Mark McCormack (Adaptavist)]

Actually April,

Would you be interested in doing a customer interview? If so, please email me using mmccormack at Adaptavist.com. I'd love to talk with you some more about how you're using ScriptRunner (if you have time).

[April]

Mark, let me know if you did not get the email

[JamieA]

April... as Mark said, thanks for your review.

> I have JIRA 6.4.11 in both staging and prod right now, BUT staging has Java 8u73, while prod

I don't think the java version will make any difference, but you are right to try to keep them consistent.

Most likely the issue doesn't have any release date?

I would change the script to this... (untested, but should work):

def versions = issue.getFixVersions()
if (versions.size() != 1) {
    return null
}

def version = versions.first()

if (!version.releaseDate) {
    return null
}

return new Date(version.releaseDate.time)

[April]

Hey I thought you would like to know that I solved my issue with Prod... I had set the template to Date Time Picker, but I forgot to change the searcher to Date Time Range picker. Apparently if you do that, the field will always render as "invalid date" in a JQL search. Doh!

[JamieA]

Oh, cool... thanks for letting us know 

[Aisha M]

@April @JamieA
Thanks for this question ! :) It really helped me fix the problem.
But, just a quick question, will this not work if there are more than one Fix version selected for an issue ? My release date shows up only when there is ONE fix version for the issue.

Any help on this please :) Thank you 

[April]

Sorry Aisha, I only noticed your comment just now.

Yes, Jira only has single date fields, so you can only have the one.

Our dev managers chose to use the first date returned, so with a bit more of Jamie's excellent advice, we went with this:

package com.acme.scriptrunner.scripts
import com.atlassian.jira.issue.Issue
Issue issue = issue
def fixVersions = issue.fixVersions
if (fixVersions) {
 def releaseDates = fixVersions.findResults { it.releaseDate }.sort()
 if (releaseDates) {
 return new Date(releaseDates.first().time)
 }
}

[Aisha M]

@April Thank you April :)