Skip to content

Sample SQL queries to query and export data from the database

← Other - ManicTime Server
ManicTime Server stores a lot of information, which you can use in your existing applications. There are two ways in which you can connect to ManicTime Server:
  • you can use SQL and get the data directly from the database
  • or you can get it via web service. To get started, take a look at a sample project

We prepared a few SQL sample queries to get you started. Run the queries in ManicTimeReports database.
In attached files you will find four sample queries:
  1. Get application usage for all users with start and end time  
  2. Get application usage by day for all users  
  3. Get tags with start and end time
  4. Get tags total by day for all users 
SQLite sample queries
PostgreSQL sample queries
MS SQL sample queries

More information about the database structure

Use only tables which start with Ar_.   
Table diagram

Table descriptions: 
Ar_Activity - a list of activities from all timelines (similar to what you see in the bottom left part when you open ManicTime client)
Ar_CommonGroup - a list of all groups from all timelines. Here you will find applications, web sites, documents... (similar to what you see in bottom right of ManicTime)
Ar_Group - Same as common group, only that some application or web site (for example Firefox) on Machine 1 will have a different id than Firefox on Machine 2. So unless you know exactly what you want, ignore this table and always use Ar_CommonGroup
Ar_Timeline - Timelines are strips of data you see in ManicTime client. By default each client has four timelines: Tags, Computer usage, Applications and Documents.
Each activity belongs to a timeline, same as in ManicTime. When you click on different timelines you get different data below. So this tells you to which timeline things belong to.
Ar_User - List of all users, you can use it to see which timeline belongs to which user.

Link productivity and custom categories to groups (use ar_CommonGroup)
Ar_Category
Ar_CategoryGroup

Ar_Environment - device information, which computer is sending the data

Ar_Folder - In ManicTime client, you can join multiple groups into a folder.  

Used for tags, each tag for example "Tag 1, Tag 2" can be from two groups (in this case group "Tag 1" and group "Tag 2")
Ar_CommonGroupList - use Ar_CommonGroupList, only use Ar_GroupList and Ar_GroupListItem if you really need to.
Ar_GroupList
Ar_GroupListItem

Map teams to users
Ar_Team
Ar_TeamMember

While you can use ar_Activity for everything, we suggest you use these tables when doing reports for longer periods.
They already contain summation for Applications, Documents and Websites and will be much quicker.
Ar_ApplicationByDay
Ar_ApplicationByYear
Ar_DocumentByDay
Ar_DocumentByYear
Ar_WebSiteByDay
Ar_WebSiteByYear

Feedback and Knowledge Base

(thinking…)