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

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

April Mar 14, 2016

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.


Anyone have a suggestion?



2 answers

1 accepted

0 votes
Answer accepted
JamieA Mar 14, 2016

Last line should be:

new Date(version.releaseDate.time)



April Mar 14, 2016

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" 



JamieA Mar 14, 2016

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

April Mar 15, 2016

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 smile

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



JamieA Mar 15, 2016

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

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 Mar 15, 2016

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?



JamieA Mar 15, 2016

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 Personally I think that makes most sense from a usability perspective.

April Mar 16, 2016

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, so perhaps that's why it is so confusing.

Can you show me how to format the ouptut?

Thanks again,


JamieA Mar 16, 2016

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


April Mar 16, 2016

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 smile), 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 Mar 16, 2016

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 Mar 17, 2016

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.


April Mar 23, 2016

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 smile

Thanks guys,


Mark McCormack Mar 23, 2016

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 Mar 23, 2016

Actually April,

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

April Mar 23, 2016

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

JamieA Mar 23, 2016

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 Apr 14, 2016

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 Apr 17, 2016

Oh, cool... thanks for letting us know wink

0 votes
Aisha M Feb 27, 2018

@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 Jun 22, 2018

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 Jun 25, 2018

@April Thank you April :) 

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Marketplace Apps & Integrations

Join our webinar: “Asset Management: The Role of Context in ITSM” with Atlassian & Riada

  Hi Atlassian Community! This is Teresa from the Jira Service Desk team. On June 19th, I’ll be hosting a webinar “Asset Management: The Role of Context in ITSM” with Atlassian Partne...

178 views 2 3
Read article

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