Field value format and export for BI

Data Center This page documents Time in status, Timer, Stopwatch and Calendar fields for Jira Data Center and Server. On Jira Cloud? See Time in Status (Forge) for Jira Cloud.

Integrations break on this page’s subject more often than on anything else in the app: a script reads the field and gets an object instead of 2h 30m, a CSV export shows JSON where the issue view showed a neat duration, or a BI model imports 9000 and nobody can say what unit that is.

All three have the same root cause - the value is computed, and what you see on an issue is a rendering of it.


The value is not stored

A Time in status, Timer or Stopwatch field does not store a duration. The value is calculated on the fly, every time it is asked for, from the issue’s history and the configuration in force.

Two consequences worth planning around:

  • There is no table you can join against to get “the time in status as of last Tuesday” - the app answers with what the current configuration computes today.
  • Changing a configuration changes what the field returns immediately, for old issues as well as new ones. Nothing has to be back-filled, and nothing can be back-dated.

What the value is

The computed value is an object, not a string. It carries:

Field What it is
time The duration, in seconds, as a whole number. This is the number, and the only number.
state The state of the field - what the counter is currently doing.
calendarId The working calendar the duration was counted by.
exportFormat The format that renders time when the value is exported.

2h 30m is not stored anywhere. It is time = 9000 put through a format.

This is why a consumer that does not know the field type - a generic CSV exporter, a third-party gadget, a script calling toString() - shows the object rather than a duration. It is not an error and nothing is missing: the consumer is printing the value as it is, instead of rendering it.

Seconds, not “1d 2h”

The raw value in an export is seconds, not a formatted string. A model that expects to parse 1d 2h out of the export is built on the display layer and will break the first time the format setting changes.

Build on time:

  • It is an integer, so it needs no locale handling, no unit parsing and no rounding rules.
  • It is stable across format changes - switching the field from D0 to D15 changes what humans read, not the number.
  • It is directly usable in a BI measure: divide by 3600 for hours, by 86400 for days.

If you do want a human-readable duration in the export instead, that is what Export Format in the field configuration is for - set it, and the export renders time through that format. See JQL Reports and export for the setting, and Time formats for the list of formats.

Pick one or the other per consumer. A BI pipeline wants the seconds; a spreadsheet a manager reads wants the formatted string. Feeding a formatted string into a BI model and parsing it back into a number is the failure mode this section exists to prevent.

Note that a format has a minimum step - a format that renders down to hours cannot render a 40-second value as anything but empty. A value that looks empty in an export but non-empty on the issue is usually this, not missing data; the field is empty checklist covers it. For the same reason a rendered duration is truncated down to that step while time stays exact, which is why the rows under a total on a panel do not always add up to it - see Truncation and totals.


Pulling report data into a BI tool

For BI, do not scrape the issue view. Build the numbers you need as a table report and take the report’s tabular export over REST.

The base REST URL of the app is {baseUrl}/rest/jibrok/timeinstatus/1.0. The tabular export of a report is served under:

{baseUrl}/rest/jibrok/timeinstatus/1.0/fastExportReport/…

It returns CSV, which every BI tool can ingest on a schedule.

The path segments after fastExportReport identify the report, so take the exact URL from the report itself rather than assembling it by hand - reports have a permalink and a CSV export, both reachable from the Reports screen.

What this gives you that a field export does not:

  • Grouping and aggregation are done by the app, not by the BI tool - see Statistics and averages in reports for exactly how AVG and the percentiles are computed.
  • The report is generated on a schedule, so the pull is cheap: the BI tool reads a finished result instead of triggering a recalculation over every issue in the JQL.
  • Access rights are the report’s own, so a BI service account can be given exactly one report.
A report holds the data of its last generation. The export returns that snapshot - it does not recompute on read. Line the report's Schedule expression up with how fresh your BI refresh needs to be. See Create report.

For eazyBI specifically there is a dedicated integration path - see Export to eazyBI.


Checklist for an integration

  1. Read time and treat it as seconds. Never parse the display string.
  2. Read state if the integration has to distinguish a running counter from a finished one.
  3. Use calendarId if two issues can legitimately be counted by different calendars - otherwise the durations are not comparable.
  4. For anything aggregate, pull a report export rather than iterating over issues.
  5. Remember that non-GET calls to the app’s REST API need an XSRF token - see REST API - XSRF and the X-Atlassian-Token header.

JiBrok time in status Time in status | SLA | Timer | Stopwatch
Migration
Release notes
Tutorials
Settings
Troubleshooting
Work calendars
Time in status
Timers and Stopwatches
Stopwatch
Timer
Additional functions
Issue panels
Reports
Field Helpers
Other fields
API
Reference information