Issue does not appear in jql query filtering by customField datepicker

Juvenal_Jose_de_Moura_Junior

Hi everybody,

I maintain a plugin for Jira 7.11vs in the company I work in, in which there is a feature that displays the issues per month. I use JqlQueryBuilder by filtering through a Datepicker customField.

Java code: 
(builder.where().And().CustomField(customFieldZZZ).gtEq(initialDate).and()
. customField (customFieldZZZ).ltEq(finalDate)

However, in October/2018, where I have the initial and final dates being 2018-10-01 and 2018-10-31 respectively, there are some issues that are not displayed, they have customField value 2018-10-31.

The same occurs when I search using jql below, that is, I do not list some issues with date 2018-10-31

JQL: cf[10702] >= 2018-10-01 AND cf[10702] <= 2018-10-31

Now, if I make a jql for November/2018, the issues with customField value 2018-10-31 are displayed

JQL: cf[10702] >= 2018-11-01 AND cf[10702] <= 2018-11-30

Please, any idea why this? 

Thanks very much

Andy Heinzer

When Jira stores date values, it's not storing just the year/month/day values.  Instead these values are being converted usually into a year/month/day hour:minute am/pm and timezone format that is actually getting stored as such in the database.  

You can confirm this for yourself if you create a custom field in Jira of type date/time.   If you call that field something like 'date1' and make sure that at least one issue has a value for that field, you can then see this by doing the following SQL command (if you're using JIRA Server, sorry Jira cloud, no sql access for you)

select cfv.issue, cfv.datevalue, cf.cfname, P.PKey || '-' || issue.IssueNum as IssueKey
from CustomFieldValue cfv
join CustomField CF on CF.Id = CFV.CustomField
join jiraissue issue on issue.id = cfv.issue
join Project P on issue.Project = P.Id
where cf.cfname='date1';

In my postgresql database, the value I have is "2018-10-31 00:00:00-05"   Even though I only entered in Jira a value of 2018-10-31.   

And given that you know that some of these issues are appearing in the JQL search results for 2018-11-01, tell me that you are being effected by a difference in timezones here.

What I suspect has happened is that you probably have users using Jira in different timezones.  And while one or more of them are entering just a date, Jira is inadvertently adjusting that date time to correspond with the timezone of other users on the site.  You could test this by changing your Jira profile's timezone and then trying to run this JQL again to see if you get different results, or see different values for these issues.   If not, then I'd be interested to see if you can use SQL to better understand the exact values in the database for these custom fields.  And then compare them with the other issue values in Jira's database to better understand how this is working.  It's possible since you're using a plugin here that the structure could be slightly different, or that the field might not be stored in exactly the same way.

But given what you have reported so far, I'm thinking this is somehow a timezone difference problem.

 

I would recommend checking out the related KBs on this topic, such as:

Incorrect Times Displayed in JIRA

Setting the timezone for the JAVA environment