Using the cases.date_modified is not the same as the date a case is closed, as the case may have been modified after it was closed.

Thus, the query below will return the date the case was transitioned to Closed provided the cases.status field is being audited.

select
  CONVERT_TZ(cases_audit.date_created,'+00:00','+10:00') as 'date_closed',
  accounts.name as 'account',
  cases.case_number,
  cases.name as 'case_id'
from
  cases_audit
  join cases on cases.id = cases_audit.parent_id
  join accounts on accounts.id = cases.account_id
where
  cases_audit.field_name = "status"
  and cases_audit.after_value_string = "Closed"
order by
  accounts.name asc,
  cases_audit.date_created desc;

Note, you might get more then one row returned per case if the case was re-opened, also my code above assumes a timezone of UTC+10

I happen to be in the UTC+10 timezone :).