JQL comparing dates with and without quotes

[Romy Vermeeren]

I run a JQL to get the amount of time spent on certain epics for specific time frames.

We just discovered that when using quotes around the dates or not impacts the result i.e. the query returns a larger amount of time spent when using the quotes.

What could explain the difference?

For reference:

with quotes:

project = "TEAM PLATFORM" AND (parent = PLATFORM-6538 OR parent = PLATFORM-6562) AND worklogDate >= "2024-09-01" AND worklogDate <= "2024-09-30" ORDER BY created DESC

without quotes:

project = "TEAM PLATFORM" AND (parent = PLATFORM-6538 OR parent = PLATFORM-6562) AND worklogDate >= 2024-09-01 AND worklogDate <= 2024-09-30 ORDER BY created DESC

[Bill Sheboy]

Hi @Romy Vermeeren -- Welcome to the Atlassian Community!

Short answer: I recommend working with your Jira Site Admin to submit a ticket to Atlassian Support to review this symptom: https://support.atlassian.com/contact/#/

The documentation for JQL with the worklogDate field, and other dates, show them in quotation marks.  When the time is not specified, it is assumed to be Midnight for the date.  I did not find any definition of different parsing with / without quotation marks.

The only related defect I found in the public backlog was using a time value with such queries, and that one is closed due to a timeout: https://jira.atlassian.com/browse/JRACLOUD-63241

Kind regards,
Bill

[Romy Vermeeren]

Thank you Bill! 

I appreciate the info and will take your advice and follow up with support.

[Jim Knepley - ReleaseTEAM]

Welcome to the community, @Romy Vermeeren 

This is a little bit of a guess, I would need to experiment to know for sure, but this feels like the date fields might have some behaviors that we need to be aware of.

When comparing the field to a string, it's convenient that your string representation of a date is sorted in chronological order. The worklogDate field could be converted to a string and the comparison done as two strings.

When the date isn't quoted, it's probably more complicated. I _think_ that a date field represents the beginning of the day (00:00:00), which might not be what you mean. You can clarify your query intention using the startOfDay() and endOfDay() functions.

I would need to experiment to understand the exact behavior.