issueFunction expression, and daylight savings time

[Greg Barwis]

In a JIRA filter, when using issueFunction in expression to calculate the minutes between two dates, the result is off by 60 minutes if the dates span a Daylight Savings Time switch (2 minutes might be calculated as 62 minutes or as -58 minutes).

Is there a workaround for this, or a way to have the query account for that? I've been able to address it with a calculated field, but I also need to be able to do this in a query directly (e.g., show me all issues where (custom date 1) is more than 30 minutes after (custom date 2), if there's a DST split in between.

Here's my scripted field, if it's useful (populates a calculated field called "Duration"):

def start = getCustomFieldValue("Incident Start Date")
def end = getCustomFieldValue("Incident End Date")
if (start && end) {
   def minutesToAdjust = Math.abs(start.getTimezoneOffset()) - Math.abs(end.getTimezoneOffset())
   return new Double((end.getTime() - start.getTime())/1000/60 + minutesToAdjust)
}
else {
 return null
}

and, if those dates cross a DST boundary, here's a query that returns results that it shouldn't (just as an example... I actually need to use a similar query in a situation where I don't have a scripted custom field):

issueFunction in expression("project = ABC", "(IncidentEndDate - IncidentStartDate) != (Duration * 1000 * 60)")

[JamieA]

Thanks for that explanation, that's very clear. That you can't use expression with script fields is: https://jamieechlin.atlassian.net/browse/GRV-695, and I hope to focus on JQL functions within the next 6 weeks. https://jamieechlin.atlassian.net/browse/GRV-713 for the DST issue.

[Greg Barwis]

Hi Jamie - This is fairly significant for us. We're using this project to track service disruptions (and manage the return-to-service workflow), so minutes count. The queries related to these fields are meant to be authoritative for informing KPIs and SLAs. The challenge with using a scripted field is that it needs to be predefined. With this particular use case, I could define (and have defined) a scripted field that accounts for this. However, we have a lot of date fields that could potentially be compared to each other, and so creating scripted fields for every possible permutation is impractical. Also, for clarification, the issueFunction query I listed above doesn't actually work - because "Duration" is in this case a Scripted Field, and Scripted Fields cannot be used inside of expression (returning the error "not a Number or Date custom field"), I can't do the compare there either. Short form is that I need users without any admin rights to be able to do these queries comparing dates, without assuming that there's an underlying field... which will also allow us to mostly work around the other issue, where we can't use calculated Scripted Fields inside of issueFunction expression.

[JamieA]

Just to help me understand, is this massively significant? Are you querying on durations that are so short they are affected by a lost or gained hour? I don't see a solution to this from the JQL side other than the code taking into account DST. But this should be solvable with a script field? You seem to be saying that is not a solution, but I'm not sure why.