Study guide
9.2 Advanced JQL Functions & Dynamic Date Searches
Atlassian Certified Professional - Jira Administration for Cloud ACP-120
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview, Blueprint & Jira Cloud Fundamentals
1.1 ACP-120 Exam Structure, Blueprint & Testing Strategy 1.2 Jira Cloud Architecture, Products & Admin Hierarchy
2 Chapter 2: Organizations, Managed Accounts & Global Access
2.1 Atlassian Organizations, Domain Verification & Managed Accounts 2.2 Atlassian Guard, SAML SSO, SCIM & Authentication Policies 2.3 Site Access, Product Access Tiers & Global Permissions 2.4 User Directory Architecture: Groups vs. Project Roles
3 Chapter 3: Project Permissions & Issue-Level Security Schemes
3.1 Permission Scheme Mechanics & Critical Project Permissions 3.2 Permission Troubleshooting, Additive Permissions & Permission Helper 3.3 Issue Security Schemes, Security Levels & Access Containment
4 Chapter 4: Project Configuration, Types, Versions & Boards
4.1 Company-Managed vs. Team-Managed Project Architecture 4.2 Project Templates, Project Keys & Project Categories 4.3 Project Components, Component Leads & Default Assignees 4.4 Versions, Fix Versions, Release Lifecycles & Project Archiving 4.5 Scrum and Kanban Board Configuration, Board Filters & Columns
5 Chapter 5: Issue Types, Custom Fields, Screens & Schemes
5.1 Standard vs. Sub-task Issue Types & Issue Type Schemes 5.2 Custom Field Types, Global vs. Project Contexts & Options 5.3 Field Configurations, Renderers, Hiding & Required Fields 5.4 Screens, Screen Schemes & Issue Type Screen Schemes
6 Chapter 6: Workflows — Company-Managed and Team-Managed
6.1 Statuses, Status Categories, Direct & All-to-One Global Transitions 6.2 Transition Rules: Conditions vs. Validators 6.3 Post Functions, Execution Order & Resolution Management 6.4 Workflow Schemes, Editing Active Workflows & Status Migration 6.5 Team-Managed Workflows, Statuses & Workflow Rules
7 Chapter 7: Jira Cloud Automation
7.1 Automation Engine, Rule Architecture & Event Triggers 7.2 Automation Conditions, Smart Values & String/Date Expressions 7.3 Branch Rules, Related Issues, Sub-tasks & Parent Epics 7.4 Rule Scopes, Automation Usage, Audit Logs & Error Handling
8 Chapter 8: Notifications, Events, Personal Settings & Mail Handlers
8.1 Jira Events, System Events & Notification Schemes 8.2 Notification Recipients, Custom User Fields & Deduplication 8.3 Team-Managed Notifications, Personal Settings & Troubleshooting Missing Email 8.4 Mail Handlers: Creating Issues and Comments from Email
9 Chapter 9: JQL, Filters, Dashboards & Bulk Operations
9.1 JQL Fundamentals, Clauses, Operators & History (WAS / CHANGED) 9.2 Advanced JQL Functions & Dynamic Date Searches 9.3 Filter Permissions, Shared Filters & Filter Subscriptions 9.4 Dashboard Layouts, the Default Dashboard, Sharing & Essential Gadgets 9.5 Bulk Operations: Edit, Move, Transition, Delete & Watch
10 Chapter 10: System Administration
10.1 Global UI Features: User Defaults, Look and Feel, Announcement Banner & Default Dashboard 10.2 Statuses, Resolutions, Priorities & Sub-tasks: Global Configuration 10.3 Global Settings: Time Tracking, Issue Linking, Attachments & Application Links 10.4 System Backup, Restore & Data Import
9.2 Advanced JQL Functions & Dynamic Date Searches
Key Takeaways Explain This Topic AI
Dynamic user functions (currentUser(), membersOf()) enable reusable, role-agnostic filters that resolve dynamically based on the viewer or group hierarchy.
Calendar functions such as startOfDay(), startOfWeek(), and startOfMonth() accept increments like "-1w"; startOfWeek() defaults to Sunday, and now() takes no arguments.
Dynamic date functions differ fundamentally from rolling window expressions (-7d evaluates the last 168 hours, whereas startOfDay("-7d") anchors to midnight 7 days ago).
Text search (~) uses Lucene stemming and ignores English reserved stop words, requiring escaped exact phrase syntax (""exact phrase"") for literal error code searches.
Jira Cloud's unified parent field natively queries sub-task parent relationships, epic-to-story associations, and advanced roadmap hierarchy levels.
Last updated: September 2026
9.2 Advanced JQL Functions & Dynamic Date Searches
Quick Summary: Writing maintainable JQL requires shifting from hardcoded, brittle values to dynamic functions that evaluate at query execution time. Jira Cloud provides built-in functions for user context ( currentUser() ), group membership ( membersOf() ), and dynamic calendar-boundary date math ( startOfDay() , endOfWeek() , startOfMonth() ). Furthermore, administrators must understand the underlying mechanics of Lucene text indexing—including stemming, wildcards, and reserved stop words—as well as Jira Cloud's modern, unified parent field for querying across multi-tier issue hierarchies.
Dynamic User & Group Functions
Hardcoding specific user names or account IDs into JQL filters creates high administrative maintenance. When team members change roles or depart the organization, every filter, board, and dashboard gadget referencing them breaks or becomes obsolete.
1. currentUser()
The currentUser() function dynamically evaluates to the Atlassian account ID of the authenticated user currently executing the query, viewing the board, or loading the dashboard gadget.
Universal Queue Pattern: Creating a saved filter defined as resolution IS EMPTY AND assignee = currentUser() allows an entire department of 500 engineers to share a single dashboard gadget ("My Open Work"). When User A views the gadget, it displays User A's tasks; when User B views it, it displays User B's tasks.
Audit & Activity Pattern: updatedBy = currentUser() or status CHANGED BY currentUser() .
2. membersOf("group-name")
The membersOf() function evaluates whether a user field contains a user who belongs to a designated Jira user group.
- Syntax: <userField> IN membersOf("group-name")
- Example Queries: /* Find all unresolved defects assigned to anyone in the frontend engineering group */
- issuetype = Bug AND resolution IS EMPTY AND assignee IN membersOf("frontend-devs")
- /* Find all security tickets reported by contractors */
- project = SEC AND reporter IN membersOf("external-contractors")
Note
The membersOf() function evaluates Jira groups (and Atlassian teams by team ID), not project roles. There's no function that returns the members of a project role. The related function spacesWhereUserHasRole() (formerly projectsWhereUserHasRole() ) returns the projects in which the current user holds a given role, for example project in spacesWhereUserHasRole("Developers") . If membersOf() would return more than 10,000 users, the search won't run.
Date Functions & Dynamic Date Math
Jira Cloud supports both absolute date filtering (e.g., created >= "2026-01-01" ) and dynamic date math. For ongoing reporting and dashboard gadgets, hardcoded dates are unacceptable because they require continuous manual updates.
Jira Cloud Calendar Boundary Functions
Function Evaluation Point (Base Timestamp) Supported Unit Modifiers Practical Example
now() The exact current date and time None (takes no arguments; use relative dates such as -1d for offsets) duedate < now() AND resolution is EMPTY (Overdue)
- startOfDay() 00:00:00 (Midnight) of today (+/-)n with d , w , m , h created >= startOfDay("-7d")
- endOfDay() 23:59:59 of today (+/-)n with d , w , m , h duedate <= endOfDay() (Due by end of today)
startOfWeek() 00:00:00 of the first day of the week ( Sunday by default ; use "+1d" for Monday) (+/-)n with w , d resolved >= startOfWeek() (Resolved this week)
endOfWeek() 23:59:59 of the final day of the week (+/-)n with w , d duedate <= endOfWeek("+1w") (Due by end of next week)
startOfMonth() 00:00:00 on the 1st day of current month (+/-)n with M , d created >= startOfMonth("-1M") (Since start of last month)
endOfMonth() 23:59:59 on the last day of current month (+/-)n with M , d duedate <= endOfMonth()
startOfYear() 00:00:00 on January 1st of current year (+/-)n with y , M created >= startOfYear() (Year-to-date creation)
- endOfYear() 23:59:59 on December 31st of current year (+/-)n with y , M duedate <= endOfYear()
- Rolling Window vs. Calendar-Boundary Anchoring
A critical distinction tested on the ACP-120 exam is the difference between relative rolling durations and calendar-boundary date functions . CURRENT TIME: Wednesday, June 17, 2026 at 15:30:00
[1] ROLLING DURATION QUERY: created >= -7d
Evaluates: Exact 168 hours prior to current millisecond.
Window: Wednesday, June 10, 2026 at 15:30:00 --> June 17, 2026 at 15:30:00
Issues created on June 10 at 09:00 AM are EXCLUDED.
[2] CALENDAR-BOUNDARY QUERY: created >= startOfDay("-7d")
Evaluates: 00:00:00 (Midnight) exactly 7 calendar days ago.
Window: Wednesday, June 10, 2026 at 00:00:00 --> Present
Issues created on June 10 at 09:00 AM are INCLUDED.
For enterprise management reporting (e.g., "All tickets logged in the past week"), rolling windows create confusion because tickets logged earlier in the day roll out of the search window mid-afternoon. Administrators should almost always choose startOfDay("-7d") or startOfWeek() for reporting consistency.
Text Searching: Lucene Mechanics, Stemming & Reserved Words
Jira Cloud indexes free-text fields ( summary , description , environment , and user comments) for searching with the ~ (CONTAINS) operator.
1. Word Stemming
The search engine applies linguistic stemming when indexing and searching English text. Stemming reduces a word to its root morphological base form:
Searching summary ~ "test" will match issues containing test , tests , testing , and tested .
Searching description ~ "connect" will match connection , connected , connecting , and connects .
2. Reserved Stop Words
To keep text search efficient, Jira ignores common English words known as stop words . These words are completely omitted from the search index:
a , and , are , as , at , be , but , by , for , if , in , into , is , it , no , not , of , on , or , such , that , the , their , then , there , these , they , this , to , was , will , with
If a user searches for an exact phrase containing reserved words using unescaped syntax: summary ~ "error in login" /* 'in' is ignored; Jira searches for 'error' AND 'login' */
This query will return issues containing "error during login", "login error", and "error when login failed".
3. Exact Phrase Searching with Escaped Quotes
When a developer or support engineer needs to find an exact, literal error string (e.g., from a stack trace or log file), they must enclose the phrase in escaped double quotes : summary ~ "\"error in login\""
The escaped quotes ask Jira to match the words together, in that order, instead of anywhere in the field.
4. Wildcard Characters and Restrictions
Jira supports two wildcard characters in text queries:
? : Matches exactly one single character (e.g., te?t matches test or text ).
* : Matches zero, one, or multiple characters (e.g., micro* matches microservice , microsoft , microchip ).
Warning
Leading Wildcards are Prohibited: In Jira Cloud text searches, a wildcard character ( * or ? ) cannot be used as the first character of a search term . Querying summary ~ "*service" will immediately trigger a JQL validation error because leading wildcards aren't supported in Jira Cloud text search.