Export Transactions from Firefly-III Database

export-firefly-iii-transactions.sql

-- Export Transactions from Firefly-III Database
-- This query produces a reasonable CSV export of transactions from a Firefly-III database.
-- Adjust the user_id if you have multiple users.
-- Tested on Postgres.
select cast(tj.date as date) as date,
       tj.description as description,
       round(tcredit.amount, 2) as credit_amount,
       acredit.name as credit_account,
       round(tdebit.amount, 2) as debit_amount,
       adebit.name as debit_account,
       c.name as category
from transactions tcredit
join transactions tdebit on tcredit.transaction_journal_id = tdebit.transaction_journal_id
join transaction_journals tj on tcredit.transaction_journal_id = tj.id
join accounts acredit on tcredit.account_id = acredit.id
join accounts adebit on tdebit.account_id = adebit.id
join category_transaction_journal ctj on ctj.transaction_journal_id = tj.id
join categories c on c.id = ctj.category_id
where tcredit.id != tdebit.id and tcredit.amount > 0
  and tj.deleted_at is null
  and tcredit.deleted_at is null
  and tdebit.deleted_at is null
  and tj.user_id = 1
order by tj.date, tcredit.transaction_journal_id;