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 · Accepted solution]

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

[Juvenal_Jose_de_Moura_Junior]

Hi Andrew
Your suspicion has been confirmed, it has to do with the time zone. We have users with two different time zones.
I consulted a problem in my postgresql (which is not being displayed) and has the date 2018-10-30 01:00:00. Interestingly, the custom field type is Date and not Date / Time.

I read the 2 kb you indicated, but I have not seen a solution that I can use here, since the time zone of the Jira application and the database are correct.

I tested other dates as 2018-07-31, 2018-08-31, 12018-12-31, 2019-01-31 and Jira saved the date correctly, without the 01:00 hour.

Do users who have a time zone other than Jira's default have to change their profile to reflect their correct time zone? Can this setting change Jira's behavior?
I do not know how I can solve this ... do you have any other ideas?

Many thanks for the reply.

[Andy Heinzer]

Could you let me know what two timezones are in use here by your end users?  And what timezone the Jira Server is using?   For a Jira Server installation, this defaults to using the timezone that the operating system is using, unless you have defined the jvm parameter on start of Jira.

Depending on your users timezones, the work-around here might just be to change the timezone that Jira's system is using to get past this.

It's much harder to force all your end users to use the same timezone instead.  That might help on one level, but it tends to confuse users in Jira when they notice that the time doesn't look correct to them.

[Juvenal_Jose_de_Moura_Junior]

We have few users who use the America / Cuiaba time zone and most of them in America / Sao_Paulo.

The Jira server is configured as America / Sao_Paulo.

The operation system is Time zone: America/Sao_Paulo (-02, -0200)

The db Postgresql is America/Sao_Paulo

[Andy Heinzer]

I would be interested to see if you can set the JVM startup parameter to use the Cuiaba timezone instead of the Sao_Paulo one.  Steps on how to do this are in the KB I mentioned above:

The value of the timezone can be altered with the below JVM argument, which is set as per our Setting Properties and Options on Startup documentation. The below example would be used to set it for America/Cuiaba:

-Duser.timezone=America/Cuiaba

I believe that this way, you might be able to avoid the fact that this appears to make this field look like it is one day off for some users using JQL.   After you make this change, you will need to restart jira for the settings to take effect.

[Juvenal_Jose_de_Moura_Junior]

Will not this change impact anyone who uses America / Sao Paulo?

[Andy Heinzer]

It has the potential to do that still.  However my thinking was that for date fields like this, the window of time that these two timezone are not both currently on the same day is only 1 hour.  By moving this back to the most westerly timezone, only date/times created at 23:00 hours Cuiaba time would appear to be the next day in Sao Paulo time.

Since Jira is defaulting to always using 00:00 timestamp included with dates in the database, I think this should help avoid the problem you are seeing with this custom field.   You still might find other date/time fields in Jira might appear to behind one day from dates set from Sao Paulo users.  

It might be worth setting up a staging environment of Jira and testing out this change before you force it out in a production system. 

If this approach would cause other problems, perhaps it might be better instead to adjust the JQL filter a bit here instead to account for the differences.

[Juvenal_Jose_de_Moura_Junior]

I will see this possibility to do in a testing environment and try to raise the impacts of the change.

I'll be back soon

Many thanks and a good weekend!