[SOL-373] How to calculate the backup duration from the system events table Created: 31.03.2016  Updated: 10.08.2020  Resolved: 10.08.2020

Status: Obsolete
Project: Solution Center
Component/s: EXASolution
Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
Fix Version/s: None

Type: How To
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: Backup, SQL

Solution:

Note: This solution is no longer maintained. For the latest information, please refer to our documentation:

https://community.exasol.com/t5/database-features/how-to-calculate-the-backup-duration-from-the-system-events/ta-p/1446

Calculate the backup duration from the system events table

with intermediate as
(
    select s.*
    , lead(event_type) over (order by measure_time)  end_event
    , lead(measure_time) over (order by measure_time)  end_time
from
    exa_system_events s
where
    event_type like 'BACKUP%'
)
select
    i.*
    , cast(trunc(measure_time, 'DD') as date) start_date
    , cast(round(minutes_between(end_time, measure_time)/60, 2) as decimal(10,2)) backup_duration
from
    intermediate i
where
    event_type = 'BACKUP_START' and end_event not like '%START'
    and measure_time > trunc(now(), 'YYYY')
order by
    measure_time desc;
Category 1: Database Administration
Category 2: Cluster Administration - Backup
Generated at Mon May 17 02:50:41 CEST 2021 using Jira 7.13.18#713018-sha1:e1230154f8ff8cc9272975bf568fc732e806fd68.