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

Question

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.

Screenshot 2014-09-24 19.43.04.png

Screenshot 2014-09-24 19.45.49.png

Nic Brough -Adaptavist- · Answer

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.

Liz BB · Answer

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

Nic Brough -Adaptavist- · Answer

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.