Blog

You are currently viewing Toggl Time entries to SQL Server

Toggl Time entries to SQL Server

Toggl is a very simple to use cloud based time tracking application. Time entries on projects and clients can be submitted through the web page or through mobile and desktop applications. Time spent by the team can be viewed in the different reports that can be accessed via their web page. There is a free version that handles much of the time tracking needs, and there are more advanced paid versions as well.

If you want to integrate the information stored at Toggl with your own enviromnent, you can use their API. One way is to download time entries to your own SQL Server database, which is the method I’ll present here.

In this solution, I’m using a SQL Server table that matches the Detailed report that can be accessed via their API. To do the actual download and insert the time entries I’m using PowerShell.

Let’s start with creating the necessary database objects. Run the following script to create the TogglDB database:

CREATE DATABASE TogglDB
GO

USE TogglDB
GO

CREATE TYPE DetailedReportType AS TABLE
	(
		id INT NOT NULL
		,pid INT NOT NULL
		,project NVARCHAR(MAX) NULL
		,client NVARCHAR(MAX) NULL
		,tid INT NULL
		,task NVARCHAR(MAX) NULL
		,uid INT NOT NULL
		,[user] NVARCHAR(MAX) NULL
		,description NVARCHAR(MAX) NULL
		,start DATETIME2 NOT NULL
		,[end] DATETIME2 NULL
		,dur BIGINT NULL
		,updated DATETIME2 NULL
		,use_stop BIT NOT NULL
		,is_billable BIT NOT NULL
		,billable FLOAT NULL
		,cur CHAR(3) NULL
		,tags NVARCHAR(MAX) NULL
	)
GO

CREATE TABLE dbo.DetailedReport
	(
		id INT NOT NULL CONSTRAINT DetailedReport_id_PK PRIMARY KEY CLUSTERED
		,pid INT NOT NULL
		,project NVARCHAR(MAX) NULL
		,client NVARCHAR(MAX) NULL
		,tid INT NULL
		,task NVARCHAR(MAX) NULL
		,uid INT NOT NULL
		,[user] NVARCHAR(MAX) NULL
		,description NVARCHAR(MAX) NULL
		,start DATETIME2 NOT NULL
		,[end] DATETIME2 NULL
		,dur BIGINT NULL
		,updated DATETIME2 NULL
		,use_stop BIT NOT NULL
		,is_billable BIT NOT NULL
		,billable FLOAT NULL
		,cur CHAR(3) NULL
		,tags NVARCHAR(MAX) NULL
	)
GO

CREATE PROCEDURE dbo.SaveDetailedReport (@DetailedReport DetailedReportType READONLY) AS

SET NOCOUNT ON

MERGE dbo.DetailedReport T
USING (SELECT * FROM @DetailedReport) AS S
	ON T.id = S.id
WHEN MATCHED THEN
	UPDATE SET T.pid = S.pid, T.project = S.project, T.client = S.client, T.tid = S.tid, T.task = S.task, T.uid = S.uid,
	T.[user] = S.[user], T.description = S.description, T.start = S.start, T.[end] = S.[end], T.dur = S.dur, T.updated = S.updated,
	T.use_stop = S.use_stop, T.is_billable = S.is_billable, T.billable = S.billable, T.cur = S.cur, T.tags = S.tags
WHEN NOT MATCHED BY TARGET THEN
	INSERT (id,pid,project,client,tid,task,uid,[user],description,start,[end],dur,updated,use_stop,is_billable,billable,cur,tags)
	VALUES (S.id,S.pid,S.project,S.client,S.tid,S.task,S.uid,S.[user],S.description,S.start,S.[end],S.dur,S.updated,S.use_stop,S.is_billable,S.billable,S.cur,S.tags);
GO

First there is the table valued parameter (DetailedReportType) that will used as an in-parameter to receive the time entries. Then there is the table (dbo.DetailedReport) matching the output from the Toggl API. Last is the stored procedure (dbo.DetailedReport) that will be called from the PowerShell script. Read more about using this method to get data from PowerShell into SQL Server here.

Before running the PowerShell script below, there are a couple of settings that needs to be configured before using the solution to call the Toggl API.

$Instance

This is the SQL Server instance where you created the TogglDB database. Format is ServerName/InstanceName.

$Email

This is the “user_agent” request parameter that is mandatory when using the Toggl Reports API. This is simply your email address used on your account.

$WorkspaceId

You can have multiple Workspaces in your account, and each Workspace has an id number that must be supplied. Check your Workspace id numbers by selecting “Workspace Settings” and the selecting the Workspace from the top of the page. The Workspace id can now be seen in the url for the page (https://toggl.com/app/workspaces/NNNNNNN/settings).

$user (Toggl API token)

This is a token that is used for authentication: You can copy your Toggl API token from the page My Profile.

$NoDaysHistory

The default setting is to return time entries for the last 6 days. This can be changed, and that is what I use the $NoDaysHistory parameter for. Note that the API will return historical records for 365 days at the most. The setting I use is to get the 30 last days. Of course, that may be quite a few records that already may have been imported to SQL Server (depending on how often the import is running), but luckily there is the field “updated” that I use to filter out records that are older than the current max date in the dbo.DetailedReport table. (Hopefully in the future there will be some way to retrieve records with an “updated” time greater that some parameter that can be supplied.)

Finally, credits to this blog post that helped in using the Invoke-RestMethod in the PowerShell script.

#############################
#Tomas Lind 2016
#http://tomaslind.net/2016/06/14/toggl-time-entries-sql-server/
#############################

#############################
#Credits to
#http://powerbits.com.au/blog/2015/7/3/use-powershell-to-access-the-toggl-api
#for how to get the "detail report" via PowerShell
#############################

cls
Import-Module SQLPS

#############################
#Settings:
$Instance = ""
$Email = ""
$WorkspaceId = 
$user = ""
$NoDaysHistory = 30
#############################

$DatabaseName = "TogglDB"
$ConnString = "Server=$Instance;Database=$DatabaseName;Integrated Security=SSPI;"

$pass = "api_token"
$pair = "$($user):$($pass)"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$headers = @{ Authorization = $basicAuthValue }
$contentType = "application/json"

$cmd = "SELECT CAST(CAST(ISNULL(MAX(updated), DATEADD(YEAR, -1, GETDATE())) AS DATE) AS CHAR(10)) AS MaxUpdated FROM TogglDB.dbo.DetailedReport"
$MaxUpdated = Invoke-Sqlcmd -Query $cmd -ServerInstance ($Instance) | select -expand MaxUpdated

$Since = (get-date).AddDays(-$NoDaysHistory).ToString("yyyy-MM-dd")

$uriReport = "https://toggl.com/reports/api/v2/details?user_agent="+$Email + "&workspace_id="+$WorkspaceId
$TogglResponse = Invoke-RestMethod -Uri $uriReport -Headers $headers -ContentType $contentType
$responseTotal = $TogglResponse.total_count
$pageNum = 1
$DetailReport = @()

while ($responseTotal -gt 0)
{
    $TogglResponse = Invoke-RestMethod -Uri $uriReport+"&page="$pageNum+"&since="$Since -Headers $headers -ContentType $contentType
    $TogglResponseData = $TogglResponse.data
    $DetailReport += $TogglResponseData | Where-Object {$_.updated -ge $MaxUpdated}
    $responseTotal = $responseTotal - 1
    $pageNum++
}

#Uncomment to see report contents
#$DetailReport | Format-Table -AutoSize | Out-String -Width 4096

$DetailReportTable = New-Object System.Data.DataTable
$DetailReportTable.Columns.Add("id", "System.Int32") | Out-Null
$DetailReportTable.Columns.Add("pid", "System.Int32") | Out-Null
$DetailReportTable.Columns.Add("project", "System.String") | Out-Null
$DetailReportTable.Columns.Add("client", "System.String") | Out-Null
$DetailReportTable.Columns.Add("tid", "System.Int32") | Out-Null
$DetailReportTable.Columns.Add("task", "System.String") | Out-Null
$DetailReportTable.Columns.Add("uid", "System.Int32") | Out-Null
$DetailReportTable.Columns.Add("user", "System.String") | Out-Null
$DetailReportTable.Columns.Add("description", "System.String") | Out-Null
$DetailReportTable.Columns.Add("start", "System.DateTime") | Out-Null
$DetailReportTable.Columns.Add("end", "System.DateTime") | Out-Null
$DetailReportTable.Columns.Add("dur", "System.Int64") | Out-Null
$DetailReportTable.Columns.Add("updated", "System.DateTime") | Out-Null
$DetailReportTable.Columns.Add("use_stop", "System.Boolean") | Out-Null
$DetailReportTable.Columns.Add("is_billable", "System.Boolean") | Out-Null
$DetailReportTable.Columns.Add("billable", "System.Single") | Out-Null
$DetailReportTable.Columns.Add("cur", "System.String") | Out-Null
$DetailReportTable.Columns.Add("tags", "System.String") | Out-Null

foreach ($Row in $DetailReport)
{
    $DetailReportTable.Rows.Add($Row.id, $Row.pid, $Row.project, $Row.client, $Row.tid, $Row.task, $Row.uid, $Row.user, $Row.description, $Row.start, $Row.end, $Row.dur, $Row.updated, $Row.use_stop, $Row.is_billable, $Row.billable, $Row.cur, $Row.tags -join ",") | Out-Null
}

$conn = New-Object System.Data.SqlClient.SqlConnection $ConnString
$conn.Open()

$query = "dbo.SaveDetailedReport"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText = $query
$cmd.Parameters.Add("@DetailedReport", [System.Data.SqlDbType]::Structured) | Out-Null
$cmd.Parameters["@DetailedReport"].Value = $DetailReportTable

$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()

You can run the script from the PowerShell ISE for a one time import. Or you can schedule the script in SQL Agent to run once per day for instance.

There is of course a lot more that could be done with the Toggl API, so let me know if you think something is missing.

Now that all time entries are imported to SQL Server, it is easy to integrate with your other applications. You can also create your own custom reports.

The duration for the time entry is stored in milliseconds. To convert that to a more meaningful representation, you can use the following view. It also has some date conversions that can be useful.

CREATE VIEW dbo.vDetailedReport

AS

SELECT
	id,
	pid,
	project,
	client,
	tid,
	task,
	uid,
	[user],
	description,
	start,
	CAST(start AS DATE) AS startdate,
	SUBSTRING(CONVERT(VARCHAR(20), start, 121),12,2) + SUBSTRING(CONVERT(VARCHAR(20), start, 121),14,3) AS starttime,
	CAST(YEAR(start) AS CHAR(4)) + SUBSTRING(CONVERT(VARCHAR(10), start, 121), 6, 2) AS startmonth,
	[end],
	CAST([end] AS DATE) AS enddate,
	SUBSTRING(CONVERT(VARCHAR(20), [end], 121),12,2) + SUBSTRING(CONVERT(VARCHAR(20), [end], 121),14,3) AS endtime,
	dur,
	dur / 1000 / 60 / CAST(60 AS FLOAT) as [hours],
	dur / 1000 / 60 as minutes,
	dur / 1000 as seconds,
	CAST(dur / 1000 / 60 / 60 AS VARCHAR(10)) + 'h' + CAST(CAST(ROUND(60 * ((dur / 1000 / 60 / CAST(60 AS DECIMAL(10,2))) % 1), 0) AS INT) AS VARCHAR(2)) + 'm' AS hhmm,
	updated,
	use_stop,
	is_billable,
	billable,
	cur,
	tags
FROM dbo.DetailedReport
GO

An example of a report is to show total hours per day along with trend lines. This way we can get early warnings if the billable hours are trending up or down for instance. To calculate MA is easy:

;WITH CTE_MS20 AS
	(
		SELECT CAST(start AS DATE) AS start, SUM(hours) AS hours FROM dbo.vDetailedReport GROUP BY CAST(start AS DATE)
	)
SELECT
	start,
	hours,
	AVG(hours) OVER (ORDER BY start ROWS 19 PRECEDING) AS MA20,
	AVG(hours) OVER (ORDER BY start ROWS 49 PRECEDING) AS MA50,
	AVG(hours) OVER (ORDER BY start ROWS 199 PRECEDING) AS MA200
FROM CTE_MS20

The result is the sum of hours per day, a short (20 days), medium (50 days) and long (200 days) trend. More about moving averages here.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has One Comment

  1. Flavio 2019/07/22 Reply

    Great work, very useful script.

    Thank you

Leave a Reply