PostgreSQL sample queries

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