User JQL help

Available JQL functions for working with time and examples of their use.

  1. This help is available from jiraā€™s help menu:

work calendars

  1. You might need a work calendar. You can see their list in the system by following the link from the help menu or this link {baseUrl}/secure/jibrokAllCalendars!default.jspa:

work calendars


Arguments used in functions.

Time - a string indicating the time, if the format is wrong, a message with help will be displayed.

"5h35m12s" - 5 hours 35 minutes 12 seconds
"5h12s" - 5 hours 12 seconds
3000m - 3000 minutes
"0", "0s" ... - time is zero.
Condition - condition for comparison of values.
"<=", "<", "=", ">", ">="

Common

Time in status

Functions for searching issues by time spent in statuses. These functions are available by default. If not, please contact your Jira admin.

  • issue in timeInStatus(ā€œstatusesā€, ā€œconditionā€, ā€œtimeā€) - Find issues that spent the specified time in statuses.
    • Params:
    • statuses - name or id, Comma separated
  • issue in timeInStatusByJql(ā€œjqlā€, ā€œstatusesā€, ā€œconditionā€, ā€œtimeā€) - Find issues from a jql request that spent the specified time in statuses.
    • Params:
    • jql - query for filter tasks
    • statuses - name or id, Comma separated
  • issue in timeInStatusByJqlAndWorkCalendar(ā€œjqlā€, ā€œstatusesā€, ā€œconditionā€, ā€œtimeā€, ā€œcalendarā€) - Find issues from a jql request that spent the specified working time in statuses. Working hours will be calculated according to the specified calendar.
    • Params:
    • jql - query for filter tasks
    • statuses - name or id, Comma separated
    • calendar - work calendar name or id for calculate work time
  • Examples:
    • issue in timeInStatus(ā€œDone, In Progressā€, ā€œ>ā€, ā€œ8hā€)
    • issue in timeInStatusByJqlAndWorkCalendar(ā€œproject = TESTā€, ā€œDoneā€, ā€œ>ā€, ā€œ3hā€, ā€œGeneral calendarā€)
  • If several statuses are specified, the search is based on the sum of the time spent in the specified statues.
  • Status names are case sensitive. If the name does not match, the function will prompt the correct spelling options for the status.
  • Use ā€œORā€ and ā€œANDā€ to search different statues: issue in timeInStatus(ā€œstatus Aā€, ā€œ>ā€, ā€œtimeā€) or issue in timeInStatus(ā€œstatus Bā€, ā€œ>ā€, ā€œtimeā€)
  • If possible use functions with jql. They narrow the search and execute faster.

Compare fields

Functions that allow you to compare different fields with each other. The functions work with ā€œnumberā€, ā€œstringā€ and ā€œtimeā€ fields. Returns issues whose fields satisfy the condition.

  • issue in compareFields(ā€œfield 1ā€, ā€œconditionā€, ā€œfield 2ā€) - Field comparison function. Finds tasks in which fields match a condition.
    • Params:
    • ā€œfield 1ā€ and ā€œfield 2ā€ - field name or field id
  • issue in compareFieldsByJql(ā€œjqlā€, ā€œfield 1ā€, ā€œconditionā€, ā€œfield 2ā€) - Field comparison function. Finds tasks in which fields match a condition and jql.
    • Params:
    • jql - query for filter tasks
    • ā€œfield 1ā€ and ā€œfield 2ā€ - field name or field id
  • Examples:
    • issue in compareFields(ā€œTime in status Newā€, ā€œ>ā€, ā€œTime in status In progressā€)
    • issue in compareFields(ā€œNumber fieldā€, ā€œ>ā€, ā€œAnother number fieldā€)
    • issue in compareFields(ā€œProject = TESTā€, ā€œString fieldā€, ā€œ>ā€, ā€œAnother number fieldā€)
  • If possible use functions with jql. They narrow the search and execute faster.
  • In the case of string fields, the function will try to convert the string to a number for correct comparison.

Compare date from field and date by work calendar

The comparisonDateFromFieldAndDateByWorkCalendar function is used to compare a date from a specified field with a computed date based on a given relative time and a specified work calendar. The behavior of the function is as follows:

  • comparisonDateFromFieldAndDateByWorkCalendar(ā€œfieldā€, ā€œconditionā€, ā€œtimeā€, ā€œcalendarā€)
    • Params:
    • ā€œfieldā€ - The name of the field to be searched and compared. It can be the name or ID of a system field or a custom field.
    • ā€œconditionā€ - The condition for comparing the date from the first field with the computed date. Possible values are <, <=, =, >=, >.
    • ā€œtimeā€ - A string representing the time relative to the current time, entered in the format ā€œ-1h 30mā€, for example. This value is used to calculate the date based on the specified work calendar.
    • ā€œcalendarā€ - The name or ID of the work calendar. The computed date is calculated based on this calendar and the specified relative time.

Behavior:

  • The function retrieves the value from the specified ā€œfieldā€.
  • The current date is computed based on the specified ā€œwork calendarā€ and the relative ā€œtimeā€ relative to the current time. For example, if the current time is 9:00 AM and relativeTime is ā€œ-1h 30mā€ (one and a half working hours ago), the computed date will represent 7:30 AM of the current working day according to the specified calendar.
  • The computed date is compared with the date from the specified ā€œfieldā€ using the specified comparison operator (ā€œconditionā€). For example, if ā€œconditionā€ is >, it checks if the date from the ā€œfieldā€ is more than the computed date .

  • Examples
    • issue in comparisonDateFromFieldAndDateByWorkCalendar(ā€œupdatedā€, ā€œ>ā€, ā€œ-1hā€, ā€œCalendar Nameā€) - find all issues that have been updated for the last working hour(-1h) on the working calendar. Like: ā€œupdated > -1hā€, but -1h by work calendar.

Autotrack

Works if the automatic time tracking function is enabled.

  • issue in myAutoTrackTickets() - Returns user issues with configured automatic timers or stopwatches.

Time in status (field)

If there is a configured field ā€œTime in statusā€, the following search functions are available for it.
You can check the field type with the administrator

Search by state

  • ā€œfield nameā€ in active() - Search by active time in status fields(issue in selected statuses)
  • ā€œfield nameā€ in inactive() - Search by inactive time in status fields(issue not in selected statuses)

Search by time

  • ā€œfield nameā€ in time(ā€œconditionā€, ā€œtimeā€) - Search by time in status fields(by total time in selected statuses)/Timer field. Time value from index.
  • ā€œfield nameā€ in realTime(ā€œconditionā€, ā€œtimeā€) - Search by time in status fields(by total time in selected statuses)/Timer field. Time value calculate realtime.
  • ā€œfield nameā€ in realTimeBetween(ā€œtimeā€, ā€œtimeā€) - Search by realtime in status fields(by total time in selected statuses). Time value calculate realtime.

  • Examples:
    • ā€œTime in newā€ in realTime(ā€œ>ā€, ā€œ20mā€) AND ā€œFirst reactionā€ in realTime(ā€œ<ā€, ā€œ1hā€)
    • ā€œTime in newā€ in realTimeBetween(ā€œ1hā€, ā€œ2hā€)

Stopwatch

If there is a configured ā€œStopwatchā€ field, the following search functions are available for it.
You can check the field type with the administrator

Search by state

  • ā€œfield nameā€ in isRunning() - Search issues by running stopwatch.
  • ā€œfield nameā€ in isPaused() - Search issues by paused stopwatch.
  • ā€œfield nameā€ in isStopped() - Search issues by stopped stopwatch.
  • ā€œfield nameā€ in active() - Returns issues that have a stopwatch is running. If the stopwatch uses a calendar, the field may indicate that it is not working hours. This does not affect the search if stopwatch is running.
  • ā€œfield nameā€ in inactive() - Returns issues that have a stopwatch is pause, stopped.

  • Examples:
    • ā€œFirst responseā€ in isRunning()
    • ā€œFirst responseā€ in isStopped()

Search by date

  • ā€œfield nameā€ in startInDay(ā€œnumberā€) - Search for issues by the day when the stopwatch was first started.
  • ā€œfield nameā€ in startInWeek(ā€œnumberā€) - Search for issues by the week when the stopwatch was first started.
  • ā€œfield nameā€ in startInMonth(ā€œnumberā€) - Search for issues by the month when the stopwatch was first started.

  • ā€œfield nameā€ in stopInDay(ā€œnumberā€) - Search for issues by the day when the stopwatch was stopped.
  • ā€œfield nameā€ in stopInWeek(ā€œnumberā€) - Search for issues by the week when the stopwatch was stopped.
  • ā€œfield nameā€ in stopInMonth(ā€œnumberā€) - Search for issues by the month when the stopwatch was stopped.

  • ā€œfield nameā€ in pauseInDay(ā€œnumberā€) - Search for issues by the day when the stopwatch was paused.
  • ā€œfield nameā€ in pauseInWeek(ā€œnumberā€) - Search for issues by the week when the stopwatch was paused.
  • ā€œfield nameā€ in pauseInMonth(ā€œnumberā€) - Search for issues by the month when the stopwatch was paused.

  • Examples:
    • ā€œFirst responseā€ in startInDay() - find the issues whose stopwatch has started today.
    • ā€œFirst responseā€ in startInDay(ā€œ-1ā€) - find the issues whose stopwatch started yesterday(1 day ago).
    • ā€œFirst responseā€ in startInDay(ā€œ-7ā€) - find the issues whose stopwatch started a week ago(7 days ago).
  • ā€œfield nameā€ in searchByStartDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the stopwatch was first started.
  • ā€œfield nameā€ in searchByPausedDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the stopwatch was last paused.
  • ā€œfield nameā€ in searchByStopDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the stopwatch was stopped.

  • date - date in ā€˜yyyy-MM-ddā€™ or ā€˜yyyy-MM-dd HH:mmā€™ format
  • Examples:
    • ā€œFirst responseā€ in searchByStartDate(ā€œ>ā€, ā€œ2021-01-01ā€) - issues for which stopwatch started after ā€œ2021-01-01ā€
  • ā€œfield nameā€ in startInDateRange(ā€œdateā€,ā€dateā€) - Search for issues by the date when the stopwatch was started (duration).
  • ā€œfield nameā€ in stopInDateRange(ā€œdateā€,ā€dateā€) - Search for issues by the date when the stopwatch was stopped (duration).

  • Examples:
    • ā€œFirst responseā€ in stopInDateRange(ā€œ2020-01-01ā€, ā€œ2021-01-01ā€) - issues for which stopwatch stopped during 2020.

Search by time

  • ā€œfield nameā€ in timeSpent(ā€œconditionā€,ā€timeā€) - Search by time in timer fields(by total time spent). Time value from index.
  • ā€œfield nameā€ in realTimeSpent(ā€œconditionā€,ā€timeā€) - Search by real time in timer fields(by total time spent). Time value calculate realtime.
  • ā€œfield nameā€ in pauseTime(ā€œconditionā€,ā€timeā€) - Search by pause time in timer and stopwatch fields.
  • ā€œfield nameā€ in realPauseTime(ā€œconditionā€,ā€timeā€) - Search by real pause time in timer and stopwatch fields.

  • Examples:
    • ā€œFirst responseā€ in realTimeSpent(ā€œ>ā€, ā€œ20mā€)
    • ā€œFirst responseā€ in realPauseTime(ā€œ<ā€, ā€œ20mā€)

Other

  • ā€œfield nameā€ in searchByRestartCount(ā€œconditionā€,ā€numberā€) - Search for issues by the stopwatch count of restart.
  • ā€œfield nameā€ in searchByCalendar(ā€œnumberā€) - Search issues by calendar id.
  • ā€œfield nameā€ in searchByStopwatchConfig(ā€œnumberā€) - Search issues by stopwatch config id.
  • ā€œfield nameā€ in searchByStopwatchScheme(ā€œnumberā€) - Search issues by stopwatch scheme id.

Timer

If there is a configured ā€œTimerā€ field, the following search functions are available for it.
You can check the field type with the administrator

Search by state

  • ā€œfield nameā€ in isRunning() - Search issues by running timer.
  • ā€œfield nameā€ in isPaused() - Search issues by paused timer.
  • ā€œfield nameā€ in isCompleted() - Search issues by completed timer.
  • ā€œfield nameā€ in isFailed() - Search issues by failed timer.
  • ā€œfield nameā€ in isFailedRealTime() - Search issues by failed timer.
    • It takes into account not only the state of the timer after stopping(FAILED/COMPLETED), but also active timers that have run out of time but state is RUNNING.
  • Examples:
    • ā€œFirst responseā€ in isFailed()
    • ā€œFirst responseā€ in isCompleted()

Search by date

  • ā€œfield nameā€ in startInDay(ā€œnumberā€) - Search for issues by the day when the timer was first started.
  • ā€œfield nameā€ in startInWeek(ā€œnumberā€) - Search for issues by the week when the timer was first started.
  • ā€œfield nameā€ in startInMonth(ā€œnumberā€) - Search for issues by the month when the timer was first started.

  • ā€œfield nameā€ in stopInDay(ā€œnumberā€) - Search for issues by the day when the timer was stopped.
  • ā€œfield nameā€ in stopInWeek(ā€œnumberā€) - Search for issues by the week when the timer was stopped.
  • ā€œfield nameā€ in stopInMonth(ā€œnumberā€) - Search for issues by the month when the timer was stopped.

  • failInDay/week/month - function shows even running timers, taking into account the estimated completion date. It also shows timers that have ā€œfailedā€ date +-N from the current day.
  • ā€œfield nameā€ in failInDay(ā€œnumberā€) - Search for issues by the day when the timer was failed.
  • ā€œfield nameā€ in failInWeek(ā€œnumberā€) - Search for issues by the week when the timer was failed.
  • ā€œfield nameā€ in failInMonth(ā€œnumberā€) - Search for issues by the month when the timer was failed.

  • ā€œfield nameā€ in pauseInDay(ā€œnumberā€) - Search for issues by the day when the timer was paused.
  • ā€œfield nameā€ in pauseInWeek(ā€œnumberā€) - Search for issues by the week when the timer was paused.
  • ā€œfield nameā€ in pauseInMonth(ā€œnumberā€) - Search for issues by the month when the timer was paused.

  • Examples:
    • ā€œFirst responseā€ in failInDay() - find issues which time in ā€œFirst responseā€ timer run out or will run out today
    • ā€œFirst responseā€ in failInDay(ā€œ-1ā€) - find issues which time in ā€œFirst responseā€ timer run out yesterday (1 day ago)
    • ā€œFirst responseā€ in failInDay(ā€œ-7ā€) - find issues which time in ā€œFirst responseā€ timer run out a week ago(7 days ago)
    • ā€œFirst responseā€ in failInDay(ā€œ7ā€) - find issues which time in ā€œFirst responseā€ timer will run out in a week(in 7 days)
  • ā€œfield nameā€ in searchByStartDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the timer was first started.
  • ā€œfield nameā€ in searchByPausedDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the timer was last paused.
  • ā€œfield nameā€ in searchByStopDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the timer was stopped.
  • ā€œfield nameā€ in searchByFailDate(ā€œconditionā€, ā€œdateā€) - Search for issues by the date when the timer was failed.

  • date - date in ā€˜yyyy-MM-ddā€™ or ā€˜yyyy-MM-dd HH:mmā€™ format
  • Examples:
    • ā€œFirst responseā€ in searchByStartDate(ā€œ>ā€, ā€œ2021-01-01ā€) - issues for which timer started after ā€œ2021-01-01ā€
  • ā€œfield nameā€ in startInDateRange(ā€œdateā€,ā€dateā€) - Search for issues by the date when the timer was started (duration).
  • ā€œfield nameā€ in stopInDateRange(ā€œdateā€,ā€dateā€) - Search for issues by the date when the timer was stopped (duration).
  • ā€œfield nameā€ in failInDateRange(ā€œdateā€,ā€dateā€) - Search for issues by the date when the timer was failed (duration).

  • Examples:
    • ā€œFirst responseā€ in failInDateRange(ā€œ2020-01-01ā€, ā€œ2021-01-01ā€) - issues for which the timer has exceeded its time during 2020.

Search by time

  • ā€œfield nameā€ in pauseTime(ā€œconditionā€,ā€timeā€) - Search by pause time in timer and stopwatch fields.
  • ā€œfield nameā€ in realPauseTime(ā€œconditionā€,ā€timeā€) - Search by real pause time in timer and stopwatch fields.
  • ā€œfield nameā€ in timeLeft(ā€œconditionā€,ā€timeā€) - Search by time left in timer fields.
  • ā€œfield nameā€ in timeSpent(ā€œconditionā€,ā€timeā€) - Search by time in timer fields(by total time spent). Time value from index.
  • ā€œfield nameā€ in realTimeSpent(ā€œconditionā€,ā€timeā€) - Search by real time in timer fields(by total time spent). Time value calculate realtime.

  • Examples:
    • ā€œFirst responseā€ in realTimeSpent(ā€œ>ā€, ā€œ20mā€)
    • ā€œFirst responseā€ in timeLeft(ā€œ<ā€, ā€œ20mā€)

Other

  • ā€œfield nameā€ in searchByZeroGoalTime() - Search for issues by zero goal time.
  • ā€œfield nameā€ in searchByRestartCount(ā€œconditionā€,ā€numberā€) - Search for issues by the timer count of restart.
  • ā€œfield nameā€ in searchByGoal(ā€œnumberā€) - Search issues by timer goal id.
  • ā€œfield nameā€ in searchByCalendar(ā€œnumberā€) - Search issues by calendar id.
  • ā€œfield nameā€ in searchByTimerConfig(ā€œnumberā€) - Search issues by timer config id.
  • ā€œfield nameā€ in searchByTimerScheme(ā€œnumberā€) - Search issues by timer scheme id.

About fields
šŸ“– Step by step - Time in status
JiBrok message field Message field
JiBrok time in status Time in status | SLA | Timer | Stopwatch
Tutorials
Settings
Work calendars
Time in status
Timers and Stopwatches
Stopwatch
Timer
Additional functions
Issue panels
Reports
Field Helpers
Other fields
API
Reference information
JiBrok Switch to User + Delegating SU (Jira) Switch to User + Delegating SU (Jira)
JiBrok time in status Time in status for Jira Cloud
Work calendars
Time in status fields
Issue panels
Reports
Service tools
Reference information
JiBrok Calculated fields Calculated fields (JBCF)
JiBrok Calculated fields Calculated fields (JBCF) for Jira Cloud
JBCF fields
Service tools
JiBrok Timer Timer field | SLA for Jira Cloud
Work calendars
Timer fields
Issue panels
Service tools
JiBrok Stopwatch Stopwatch for Jira Cloud
Work calendars
Stopwatch fields
Issue panels
Service tools
JiBrok Time between dates Time between dates for Jira Cloud
Time between dates fields
JiBrok Fields panel for Jira Service Management (JSM) Fields panel for Jira Service Management (JSM)
JiBrok Display Linked Issues Display Linked Issues
User helper for JiBrok cloud apps User helper for JiBrok cloud apps