How to use date filters in JQL

[simarpreet singh]

Hi team,

I need to understand what does it mean by saying created>=-2h or updated <1d

Basically, I want to understand WHEN to use > and when to use <..... And when to use negative (-) and when to use positive (+) like -2d or +1d

Hope you got my needs. I know this may be a very basic question for most of you, but any help and even a documentation would be appreciated.

As a final ask, my requirement was basically to find out issues that were created between 2 am in the morning till 6 am the same day

Thanks in advance,

Simar

[Payne · Accepted solution]

> means after (literally the timestamp is greater, so it's later), and < means before (timestamp is smaller, so it's earlier).

Similarly, - means before, and + (or lack of sign) means after.

So, in your example of created >= -2h, that means created is after 2 hours ago, or within the last 2 hours.

Updated < 1d means updated is before 1 day from now (one day in the future), which will be all of your issues.

For your requirement, you could use this:

created >= startOfDay(2h) and created < startOfDay(6h)

startOfDay() is defined as midnight, so startOfDay(2h) means 2 hours after startOfDay(), or 2:00am. Similarly startOfDay(6h) means 6 hours after startOfDay(), or 6:00am.

Hope this helps! 

[simarpreet singh]

Thanks a lot. It helps.

[Constanze Schmidt]

Hi Payne,

to clarify your answer: So I can either work with '>/<' or with ' -/+',  that is 'created >=- 2h' or 'created <= 2h' means the same, right?

Is there a preference / recommendation on what to use? 

And another question:

My query: 'duedate <= 6w' should list all issues where the due date is older than 6 weeks.  But it does not work correctly: I also get issues where the due date is just 3 or 2 weeks overdue.   

Would be great if you could help me with that:-)

[Payne]

Hi Constanze,

No, created >= -2h and created <= 2h are not interchangeable; try them both, and you'll see.

Similarly, duedate <= 6w is not correct for finding issues where the due date is older than 6 weeks; instead, you should use duedate >= -6w

-Payne

[Constanze Schmidt]

Hi Payne, thanks for clarifying:-)

Obviously I can´t quite get my head around this🙈. 

Did I get it right?

• Created >= -2h:  created after 2 hours ago
• Created <= 2h: created between now and 2 hours from now
• Created >= 2h: created after 2 hours from now
• created <= -2h: created between 2 hours ago and now

and  

• Duedate <= 6w:   Due between today and six weeks from now  (between 30.04.2021 and 15.05.2021)
• Duedate <= -6w:  Due 6 weeks ago or earlier (before 14.03.2021)
• Duedate >= 6w:  Due in six weeks from now on or later (after 15.05.2021)
• Duedate >= -6w:  Due between today and 6 weeks earlier (between 14.03.2021 and 30.04.2021)

[Payne]

The best way I suggest for you to confirm these is to try them and sort the results by the relevant field (e.g. created >= -2h ORDER BY created asc [or desc]) and view the oldest and newest issues; that'll help you confirm whether each query is behaving as you think it is.