Why does the Datetime value stored by Date Picker custom field is broken?

I was surprised to discover that for the the Date Picker custom fields (those that do store only the date, without the time) the value stored in side tha PostgreSQL database are.... mind-blowing.

Screenshot 2014-09-24 19.45.49.png


select datevalue from customfieldvalue where datevalue is not NULL 
and  customfield = 10012 LIMIT 20 /* replace custom field # with yours */

As you can see the values here are clearly broken, is like JIRA did save the value by embedding the timezone difference of the user into the date value and forced the timezone to be GMT.

Due to this, any query on the database will display wrong values, and is impossible to fix them because you would have to know the timezone of the used that edited the custom field last time. 

What is strange is that this problem doesn't seem to affect the user interface of JIRA, which seems (not sure) to display the correct values, at least for some of the records.


1 answer

1 vote

The values aren't "broken", they're stored in a consistent format that allows JIRA to translate them into what the users expect to see for their chosen timezone. 

The short answer here is that if you're going to read the database, you need to reconstruct the display/business logic that JIRA implements.

I have a related question that I hope you can help me with. I am pulling a Date Picker custom field on a post function custom email. The problem is the value is also showing the timestamp 00:00:00.0. Is there a way to only pull the date value? Right now I am using the following to pull the date picker custom field: <% out << issue.getCustomFieldValue(componentManager.getCustomFieldManager().getCustomFieldObjectByName("[CUSTOM FIELD NAME])) %> Should something like this work? <% out << issue.getCustomFieldValue(componentManager.getCustomFieldManager().getCustomFieldObjectByName("[CUSTOM FIELD NAME])).displayDate %>

There is no difference between a date and a date/time field in the backend. The difference between the fields is all done at the display level - when using a date/time field, there's some code to enter and show time, and when you're using a date field, there is nothing for the time part and it assumes midnight. To get just the date out of either type of field, you need to strip the time segment out of the string that you've got.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

957 views 3 9
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you