Let's take a look at using date fields and operators with Jira's query language. There are a few 'gotchas' to watch out for when using date values. Having said that there are also some really useful functions that will help you identify exactly what you need from your date based JQL queries
Now the most straight forward example is something along the lines of
project = "DX" and createdDate > 2023-7-03
Important to take into account that “greater than" is taken as the start of day 23-08-03.
Jira JQL with Dates
So really this query really means
project = "DX" and createdDate >= 2023-07-03
For clarity and to avoid confusion sometimes it's better to work with times as well. Adds a bit of clarity to the greater than and less than conditions.
project = "DX" and createdDate > '2023-08-03 00:00'
This way there's no mistaking that you're including issues that are created on the 3rd of August (after 00:00hrs on the 3rd of August).
Of course you can narrow this down further with another date condition:
project = "DX" and createdDate > '2023-07-03' and createdDate < 2023-08-03
And here's my point (if you don't use the time the date value defaults to a time of 00:00:00 )
Jira JQL Date Range
Again, at the risk of repeating myself, it's best to use the time in the expression to improve clarity.
project = "DX" and createdDate > '2023-07-03 00:00:00' and createdDate < '2023-08-03 23:59:59'
Which gives us everything create on the 3rd July through to AND including created on 3rd Aug.
Relative Dates
You can also use relative dates. That is plus/minus a number of days relative to a fields absolute date. You do this with this nomenclature
project = "DX" and createdDate > -7d
Where the 'minus' is considered as days before the createdDate. The 'd' character obviously meaning days. So created AFTER the date 7 days ago. You can use any relative date that conforms to this syntax:
(+/-)nn(y|M|w|d|h|m)
Where…
y = years
M = months
w = weeks
d = days
h = hours
m = minutes
A date in the future is written as “7d". So this would be seven days in the future
project = "DX" and createdDate > 7d
Which is a pretty nonsense bit of JQL …. unless that is …. you've learnt to time travel and create issues in the future!
Something more logical here would be using the “due date"
project = "DX" and dueDate > 1w
Which is due “after" 1 week from now. Or we could change this to …
project = "DX" and dueDate > now() and dueDate < 1w
JQL for Issues Due Between Dates
Which is a little more helpful. That’s partly because we’ve used the `now()` date function.
Date Functions
Date functions allow us to find dates without having to specify a hard coded date. Very useful if your JQL is being used in a filter or the search criteria behind a dashboard gadget. There are a few more that'll come in handy:
endOfDay()
endOfWeek()
endOfMonth()
endOfYear()
startOfDay()
startOfMonth()
startOfYear()
Note that the startOfWeek() function considers Sunday to be the first day of the week.
All of these, including now(), accept the adjustments as parameters too…
(+/-)nn(y|M|w|d|h|m)
This can all be summarised with
Jira JQL Date Functions
So this JQL …
project = "DX" and dueDate > now() and dueDate < endOfWeek(3w)
You can think of this as “up to 3 end of the weeks".
Which is very different from this
project = "DX" and dueDate > now() and dueDate < 3w
Which is “3 weeks from today".
One last scenario you might find useful then. Finding issues where the status has changed within a date range.
project = "DX" and status CHANGED TO "In Progress" AFTER startOfWeek()
What we're doing here is using the “CHANGED TO" term that allows your JQL to drill into the history of your issues. Searching for changes to status.
If you want to get a little more focused could also try the “CHANGED FROM" term:
project = "DX" and status CHANGED from "In Progress" to "Done" AFTER startOfWeek(-1w)
Which gives us all the issues we started working on last week and this week.
There's nothing complicated with using dates in JQL. You just need to be familiar with the syntax and watch out for a few gotcha's!
