Extract logged time from published reports via SQL

Use this article to extract logged time from Reports and Timesheets for Jira (Data Center) published reports directly from the database.

When a report is published in the app, the report results are saved as a snapshot in the database. Administrators may need to extract this data via SQL for troubleshooting, auditing, or external reporting purposes.

Instructions

1. Confirm the environment

Before running the query, confirm that your environment matches the following requirements:

Component	Requirement

Component	Requirement
App	Reports and Timesheets for Jira
Platform	Jira Data Center / Server
Database	PostgreSQL, or another SQL database with JSON support
Access	Read-only database access is recommended

Run database queries carefully. When possible, use read-only access or test the query in a staging environment before running it against production.

2. Locate the published report data

Published report results are stored as JSON data in the payload column of the add-on's published report table.

The primary table used in this example is:

"AO_8B1089_PUBLISHED_REPORT"

The query extracts values from the payload JSON field and converts them into readable columns.

3. Run the SQL extraction query

Use the following SQL query to extract project, task, user, logged time, and log date details from published report results:

SELECT 
    json_extract_path_text(payload::json, 'project') AS project,
    json_extract_path_text(payload::json, 'task') AS task,
    json_extract_path_text(payload::json, 'user') AS username,
    json_extract_path_text(payload::json, 'timeLogged') AS logged_time,
    json_extract_path_text(payload::json, 'logDate') AS log_date
FROM "AO_8B1089_PUBLISHED_REPORT"
WHERE payload IS NOT NULL;

This query is written for PostgreSQL. Other database types may require different JSON extraction syntax.

4. Review the extracted results

The query returns the following fields:

Field	SQL Alias	Description

Field	SQL Alias	Description
`project`	`project`	Jira project associated with the worklog
`task`	`task`	Jira issue key or task associated with the worklog
`user`	`username`	Username of the person who logged the time
`timeLogged`	`logged_time`	Duration of time logged
`logDate`	`log_date`	Date the work was performed

5. Use the extracted data for auditing or troubleshooting

Use the query results when you need to:

Audit historical published reports in bulk.
Compare app data with external database records.
Access report data when the app UI is unavailable.
Extract large report results that can't be exported from the UI.

Export the SQL results to CSV if the data needs to be shared with Support, reviewed by an administrator, or imported into another reporting tool.