SQLite 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, 
    date(gt.Hour) 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, 
    date(gt.Hour), 
    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(strftime('%s', a.EndLocalTime) - strftime('%s', 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