-- Get application usage for all users with start and end time  
-- owner, application, start, end
select 
    u.UserId OwnerId, 
    u.DisplayName "Owner", 
    a.Name ActivityName, 
    a.CommonGroupId CommonApplicationId, 
    cg.Name ApplicationName, 
    a.StartLocalTime, 
    a.EndLocalTime
from 
    Ar_Activity a
join 
    Ar_CommonGroup cg on a.CommonGroupId = cg.CommonId
join 
    Ar_Timeline t on a.ReportId = t.ReportId
join 
    Ar_User u on u.UserId = t.OwnerId
where 
    t.SchemaName = 'ManicTime/Applications' and
    a.StartLocalTime < '2016-05-16' and
    a.EndLocalTime > '2016-05-09'

-- Get application usage by day for all users  
-- owner, day, application, Total seconds
select 
    u.UserId OwnerId, 
    u.DisplayName "Owner", 
    gt.Hour::date "Day", 
    cg.CommonId CommonApplicationId, 
    cg.Name ApplicationName, 
    sum(gt.TotalSeconds) TotalSeconds
from 
    Ar_ApplicationByDay gt
join 
    Ar_CommonGroup cg on cg.CommonId = gt.CommonId
join 
    Ar_Timeline t on gt.ReportId = t.ReportId
join 
    Ar_User u on u.UserId = t.OwnerId
where 
    gt.Hour >= '2016-05-09' and gt.Hour < '2016-05-16'
group by 
    u.UserId, 
    u.DisplayName, 
    gt.Hour::date, 
    cg.CommonId, 
    cg.Name

-- Get tags with start and end time
-- owner, tag, notes, billable, start, end
select 
    u.UserId OwnerId, 
    u.DisplayName "Owner", 
    a.Name Tag, 
    a.StartLocalTime, 
    a.EndLocalTime
from 
    Ar_Activity a
join 
    Ar_Timeline t on a.ReportId = t.ReportId
join 
    Ar_User u on u.UserId = t.OwnerId
where 
    t.SchemaName = 'ManicTime/Tags' and
    a.StartLocalTime < '2016-05-16' and
    a.EndLocalTime > '2016-05-09'    

-- Get tags total by day for all users 
-- owner, day, tag, notes, billable, Total seconds
select 
    u.UserId OwnerId, 
    u.DisplayName "Owner", 
    a.Name Tag, 
    a.Notes,
    a.IsBillable,
    sum(extract(epoch from (a.EndLocalTime - a.StartLocalTime))) TotalSeconds
from 
    Ar_Activity a
join 
    Ar_Timeline t on a.ReportId = t.ReportId
join 
    Ar_User u on u.UserId = t.OwnerId
where 
    t.SchemaName = 'ManicTime/Tags' and
    a.StartLocalTime < '2016-05-16' and
    a.EndLocalTime > '2016-05-09'
group by
    u.UserId,
    u.DisplayName,
    a.Name,
    a.Notes,
    a.IsBillable