JQL Date Format Guide: Simplify Your Jira Queries

Have you ever tried writing queries in Jira only to meet frustrating errors or irrelevant results? One of the most difficult aspects of Jira Query Language (JQL) is the effective handling of date fields.

Whether you’re filtering issues by custom fields like “Actual Start Date,” tracking when status changed to “Closed,” or comparing timelines to ensure deadlines are met, even a minor formatting error can render your query invalid or unreliable.

The problem is with Jira’s very restrictive date format and syntax. Without a solid understanding of these rules, it’s easy to misstep. 

That is the reason why our guide is written with the proper JQL date format: to enable you to write accurate queries and prevent mistakes from happening throughout JIRA. 

Also, how tools like JQL generators from PaceAI can help you with query generation within a few minutes. 

What is the JQL Date Format?

JQL (Jira Query Language) is a simple, straightforward, and versatile query language. It was built by Atlassian specifically for the JIRA tool, where project managers can search for solutions based on a variety of criteria, including time-based conditions. 

So, before you begin working with these, you must understand how JQL handles date formats, especially if you want to search for time-bound conditions in JIRA. Knowing them will help you in creating accurate and effective queries.

In terms of time and date formats, Jira supports two main types of data inputs:

1. Absolute dates 
2. Relative periods

Absolute dates follow specific formats like yyyy/MM/dd or yyyy-MM-dd, which refers to the fixed dates. Meanwhile, relative dates allow the JIRA users to define time ranges dynamically based on the current date. For example, its format is 4w 2d (four weeks and two days ago).

However, working with these formats comes with its challenges. 

For example, you will struggle when mapping a custom field to a generalized date operation, figuring out how functions such as startOfDay() work, or combining absolute and relative formats all into one query can result in irrelevant results. 

Thus, the ability to understand the rules and to master these aspects allows you to build queries with certainty in order to obtain the correct data without errors or incoherence.

For example:

• Absolute date format: due >= “2023/11/01”
• Relative period format: created >= -4w (issues created in the last four weeks)
• Date functions: Predefined dynamic points in time (e.g., today(), startOfWeek() )

Importance of Date Functions in JQL

JQL date functions are powerful tools for writing correct, dynamic, context-aware queries.

These functions will allow you to reference key points in time, like the start of the current day or the end of a week, without having to update your queries every time manually. 

So you can write and save these date functions, which makes your filters adaptable, precise, and easier to maintain for a steady and effective workflow in the long term.

endOfDay()

endOfWeek() 

endOfMonth()

endOfYear()

startOfDay()

startOfMonth()

startOfYear()

Why JQL Date Formats Matter for Your Workflow?

In JIRA, logging in a start and an end date is not that simple. The time fields in JIRA are sensitive to a strict format, and only if you write the date in the correct format, then will it be registered. Otherwise, it will throw an “Invalid Date Format” error repeatedly. 

Sometimes, there are missteps in date formatting that can lead to incomplete data retrieval or entirely missed issues, which can disrupt your workflows and delay critical decision-making. 

Hence, understanding the right date format for your JIRA can significantly impact your project management processes. Here are some specific scenarios within your workflow where JQL date formats play a huge role. 

1. Track Progress on Specific Timelines

When managing projects, precision in tracking progress is crucial. With accurate date queries, you can:

• Detect issues that were introduced, modified, or fixed over a certain period of time.
• Monitor sprint milestone delivery by identifying tasks completed before and after important dates.
• Identify workflow bottlenecks, such as those that are unclosed after a deadline has passed.

For example:

status = “In Progress” AND updated >= startOfWeek()

This query provides real-time updates on tasks that have seen progress this week, helping you stay on top of team productivity.

2. Generate Precise Reports for Stakeholders

You can generate time-sensitive reports with precise details for the stakeholders. You can also showcase them on dashboards that display issues resolved within the last quarter or sprint.

This way, you will be able to track and compare time-to-resolution metrics across teams or projects and stay at the frontier of effective and productive project management.

Example:

resolved >= “2023/01/01” AND resolved <= “2023/03/31”

This query captures all issues resolved in the first quarter of 2023, providing clear data for quarterly reporting.

3. Streamline Issue Tracking

Whether you’re managing ongoing initiatives or reviewing old workflows, let’s say 2023-edited workflows, then the JQL date formats can help you streamline issue tracking by:

Isolating overdue tasks with queries like:
due < now() AND resolution = Unresolved

And then highlighting issues reopened after a specific date to identify recurring problems:
status = “Reopened” AND updated >= startOfMonth()

This will help you analyze workflow efficiency by combining date-based filters with assignees or issue types.

4. Enable Dynamic Querying with Date Functions

JQL date functions like startOfDay(), endOfWeek(), or now() allow for dynamic queries that adapt to your current context. For instance:

• A query such as created >= startOfDay() helps monitor issues created today.
• Functions like endOfWeek() help teams plan and assess what needs to be resolved by the weekend.

These dynamic queries eliminate the need for constant manual updates, making sure your queries stay relevant over time.

JQL Date Formats: Key Syntax and Examples

Understanding the correct syntax for JQL date formats is very important before you even begin writing effective queries. Below, we have listed both basic and advanced syntaxes, making sure they are accurate and practical for real-world Jira use cases.

Basic Syntax for JQL Date Formats

1. Absolute Dates

Absolute dates refer to fixed calendar dates in specific formats like yyyy/MM/dd or yyyy-MM-dd. These are used for static queries where the date does not change.

Find issues created on or after a specific date:
created >= “2023/11/01”

Filter issues due before a specific date:
due < “2023/12/31”

2. Relative Dates

Relative dates dynamically adjust based on the current date. Use periods like days (d), weeks (w), or hours (h) for flexibility.

Find issues updated in the last 7 days:
updated >= -7d

Filter issues created more than 30 days ago:
created <= -30d

3. Date Functions

Jira supports functions like startOfDay(), endOfWeek(), and now() for dynamic date filtering.

Fetch issues resolved today:
resolved >= startOfDay()

Retrieve issues updated this week:
updated >= startOfWeek()

Find issues due by the end of the month:
due <= endOfMonth()

Advanced Syntax for JQL Date Formats

1. Combining Absolute and Relative Dates

Combine absolute and relative dates for complex queries.

Find issues created in the last 30 days but resolved by a specific date:
created >= -30d AND resolved <= “2023/11/15”

Retrieve issues updated after the beginning of this year and unresolved:
updated >= “2023/01/01” AND resolution = Unresolved

2. Tracking Status Changes with Dates

Use status changed to filter issues based on when their status transitioned.

Find issues that changed to “In Progress” in the last 14 days:
status changed to “In Progress” after -14d

Filter issues that changed to “Done” after a specific date:
status changed to “Done” after “2023/11/01”

3. Filtering by Custom Date Fields

Custom fields can also be queried if they store date information.

Fetch issues where a custom field (Start Date) is after November 1, 2023:
“Start Date” > “2023/11/01”

Find issues where a custom field matches the current date:
“Start Date” = startOfDay()

4. Combining Date Functions with Other Filters

Combine date functions with additional filters like priorities, assignees, or labels.

Find high-priority issues created in the last week:
created >= -7d AND priority = High

Filter unresolved issues assigned to the current user and due this week:
resolution = Unresolved AND assignee = currentUser() AND due <= endOfWeek()

5. Date Ranges

Specify a range to retrieve issues within a specific time frame.

Fetch issues created in the first quarter of 2023:
created >= “2023/01/01” AND created <= “2023/03/31”

Retrieve issues updated between 7 and 30 days ago:
updated >= -30d AND updated <= -7d

6. Handling Reopened or Transitioned Issues

Track issues that have been reopened or transitioned based on dates.

Find issues reopened in the last week:
status changed to “Reopened” after -7d

Filter issues transitioned from “To Do” to “In Progress” this month:
status changed from “To Do” to “In Progress” after startOfMonth()

Tips for Using JQL Date Formats Efficiently

Use Absolute and Relative Dates wisely

In JIRA, although you can use a mix of formats for flexible queries, it’s recommended that you choose them appropriately. Use absolute dates when you need fixed, unchanging time frames, such as generating historical reports or querying specific milestones. 

However, if you need ongoing or dynamic queries, like tracking issues updated within the last week, then using relative dates will work.

Example: created >= “2023/11/01” AND updated >= -7d

Use Date Functions for Dynamic Filters

Functions like startOfDay() and endOfMonth() simplify dynamic queries and eliminate the need to update queries manually.

Examples:

Monitor daily activity:
created >= startOfDay()

Filter issues due by the end of the current month:
due <= endOfMonth()

Track weekly progress:
updated >= startOfWeek()

Use Parentheses for Complex Queries

When combining multiple conditions, group them with parentheses to avoid ambiguity. Parentheses help structure queries with multiple conditions, ensuring the proper execution of logical operators.

Example: (created >= -30d OR due <= “2023/12/31”) AND assignee = currentUser()

Validate Custom Fields

When querying custom date fields, ensure:

• The field is correctly defined in Jira (e.g., “Start Date”).
• The values follow valid formats for JQL.

Example:

Issues where the custom field “Start Date” is in the past:
“Start Date” <= now()

Use Dashboards to Visualize Date Queries

Create Jira dashboards with saved queries to monitor date-based metrics in real time. Use gadgets to:

• Display issues resolved within a time frame.
• Highlight overdue tasks using filters like:

due < now() AND resolution = Unresolved

Test and Optimize Queries

Always test your queries before deploying them widely:

• Use smaller subsets or projects to verify results.
• Review the output to ensure it meets your expectations.

How PaceAI Simplifies JQL Queries

Manually writing JQL queries can be daunting, especially for new users. PaceAI simplifies this process by offering:

• AI-driven suggestions
• Error prevention
• Custom reports

Here’s how it works:

• PaceAI’s JQL Generator automates the process of building Jira Query Language (JQL) queries, allowing users to easily create accurate searches. Here’s how to use it:
• Log in to your PaceAI dashboard and navigate to the JQL Generator tool.
• Provide a descriptive account of the problem or data that you would like to obtain. For example, To search for all high-priority bugs created in the past 30 days, you can type: “Find all high-priority bugs created in the last 30 days.
• Click the ‘Generate’ button.

Conclusion

Mastering JQL date formats empowers you to craft precise queries for better project tracking and reporting. Whether you’re tracking issues from 2023-edited workflows or planning future sprints, leveraging date functions, period formats, and tools like PaceAI ensures your Jira queries are both accurate and efficient.

FAQs