I’m working on creating JIRA filters using JQL for the createdDate field. Right now I can only use the Now() function with relative dates like “-2d” or “-3d”.
The issue is that Now() uses the current timestamp, so I can’t filter for complete calendar days. For example:
createdDate >= "-1d" AND createdDate < Now()
When I run this filter at 3PM today, it shows tickets from 3PM yesterday until 3PM today. If I run it at 10AM tomorrow, it shows tickets from 10AM today until 10AM tomorrow.
What I really need is to get all tickets created during a full calendar day (from midnight 00:00 to 23:59). Is there a JQL function that works with calendar dates instead of 24-hour time periods? I want consistent results regardless of what time I execute the query.
just use createdDate = startOfDay() for today’s tickets. for yesterday, it’s createdDate = startOfDay("-1d"). way simpler than those complex AND statements and gets you exactly what you need.
Been fighting this exact issue for months in our reporting pipeline. JIRA’s timezone config actually messes with how startOfDay() works - nobody ever mentions this. If your server timezone doesn’t match your local one, you’ll get weird results even with startOfDay functions. Check General Configuration in your JIRA system settings to see what timezone it’s using. Had tickets showing up in the wrong day buckets until I figured out our server was UTC while we’re running EST. Workaround: adjust your relative day calculations - use startOfDay(“-2d”) when you actually want yesterday’s data.
You can also use specific date formats in JQL instead of relative functions. Try createdDate >= "2024-01-15" AND createdDate < "2024-01-16" for exact calendar day boundaries. You’ll need to update dates manually, but it’s absolutely precise. This works great for automated reports where you can generate date strings programmatically. Less flexible than relative dates, sure, but it cuts out any timezone or timing issues you sometimes get with day functions.
Skip the JQL headaches and automate it instead.
I built a workflow that pulls JIRA data daily through their REST API. You get full control over date filtering without dealing with JQL’s weird quirks.
Set up automation to query JIRA at midnight. Filter by parsing createdDate directly in your code. Dump results wherever - spreadsheets, databases, other tools.
No more timezone issues, and you get consistent data whether someone runs it manually or not. Custom filtering logic beats JQL every time.
I’m using this setup for several clients now. Way more reliable than crossing your fingers that JIRA functions won’t break after updates.
Check out https://latenode.com - their JIRA integration handles the API calls and scheduling perfectly.
You can combine startOfDay() and endOfDay() functions. Just use createdDate >= startOfDay("-1d") AND createdDate <= endOfDay("-1d") to grab all tickets from yesterday. This gives you consistent results no matter when you run it since it uses midnight boundaries instead of the current timestamp. I use this for quarterly reports and it works great across different JIRA Cloud instances. The endOfDay function catches everything up to 23:59:59 of that date.
try startOfDay() instead of now(). use createdDate >= startOfDay("-1d") to get the full calendar day from midnight. haven’t tested this across all jira versions tho, so check your instance first.