---
title: "Extract logged time from published reports via SQL"
canonical: "https://support.appfire.com/space/SUPPORT/3228041258/Extract%20logged%20time%20from%20published%20reports%20via%20SQL"
format: markdown
---
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 |
| --- | --- |
| 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 |
| --- | --- | --- |
| `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.