Stephan Meijer · Personal Tech projects and such

Apostolos with Redash: Analytics of your time being tracked

Previously I have been writing about Apostolos.

Apostolos: bringing the Good Word of proper Time Tracking

Track time easily with Apostolos

Apostolos is a small commandline interface to help you tracking time in Calendar applications such as Google Calendar or NextCloud. One of my goals with this application is creating an easy interface to interact with the specific Calendar application you are using.

I recently added another command to this package: time:export. This command exports all the calendar data to JSON, which then can be read into applications such as Redash. Using Redash, you can create graphs of your hours worked.

Given you have three clients, Apostolos can export the registered hours. Using time:export, your data would be in the following JSON format:

Click to expand for code.
[
    {
        "day": "2021-11-10",
        "duration": 6.25,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-11",
        "duration": 1.67,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-12",
        "duration": 1.5,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-15",
        "duration": 5.20,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-17",
        "duration": 0.9,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-18",
        "duration": 3,
        "calendar": "Client A"
    },
    {
        "day": "2021-11-08",
        "duration": 5.5,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-09",
        "duration": 7,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-10",
        "duration": 4.5,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-11",
        "duration": 9.5,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-12",
        "duration": 8.75,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-15",
        "duration": 4,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-16",
        "duration": 3,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-17",
        "duration": 5,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-18",
        "duration": 6,
        "calendar": "Client B"
    },
    {
        "day": "2021-11-19",
        "duration": 7,
        "calendar": "Client B"
    }
]

Redash can be used to create a Graph from this data.

Creating the API

In order to display the data in Redash, we would need an API to respond with the appropriate data. This is an example implementation one could use to achieve it.

This simple program saves POST data from cURL to a file to later display it on HTTP GET requests.

<?php

/**
 * You can also use a database for this with `password_hash`.
 */
$validUsername = "admin";
$validPassword = "GrbG9ruEWjm9yDxHcN34yx6wZA33JF";

function askToAuth() {
    header('WWW-Authenticate: Basic realm="JSON Storage"');
    header('HTTP/1.0 401 Unauthorized');
    exit;
}

/**
 * Require user to authenticate before accessing the page
 */
if (!isset($_SERVER['PHP_AUTH_USER'])) {
    askToAuth();
}

$user = $_SERVER['PHP_AUTH_USER'];
$pass = $_SERVER['PHP_AUTH_PW'];

if ($user != $validUsername || $pass != $validPassword) {
    askToAuth();
}

header("Content-Type: application/json");

$path = __DIR__ . "/data.json";

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    file_put_contents(
        $path,
        file_get_contents('php://input')
    );
}

echo file_exists($path) ? file_get_contents($path) : "[]";

Synchronizing to the API

You can now upload data from Apostolos to this API using the following cURL command:

$ bin/console time:export | curl \
    -H "Content-Type: application/json" \
    -X POST \
    -d "$(</dev/stdin)" \
    "http://admin:GrbG9ruEWjm9yDxHcN34yx6wZA33JF@localhost:8082/"

Ofcourse, you should replace the username and password to your own specific wished for credentials.

Adding the Data Source

Add the Data Source with credentials as above.

Redash: Data Source

Adding the Query

I work locally. Redash didn’t seem to be able to query private addresses. I am not sure why not.

Redash: query private address error

That’s why I temporarily put up an ngrok proxy. In production, you can just publish the script to regular webhosting or host it yourself, as long as it has a public hostname.

ngrok: setup proxy

Now we can change the url to the URL provided by ngrok.

Redash: execute query

As you can see, the data now shows up. This means our API works and that Redash correctly authenticates with it. That also means our API is safe to the outside world as it requires Basic Authentication.

When running in production, make sure /data.json is not accessible to the outside world. Depending on your webserver, you could do this by configuring a .htaccess file and/or a .htpassword file.

Adding the visualisation

You can now click on “Add Visualization”

Redash: setup visual

This might be a bit overwhelming, but the steps from here are the easiest ones. First, click for X Column on day and for Y Column on duration.

Redash: setup visual

As you can see, Redash sums up all hours worked, independent from the source calendar. We can easily fix this using the Group by function.

Redash: setup visual

Neat! Now it shows the data we want. But what if we want to see a stacked version of this? Easy! Just scroll down and use the Stacking option.

Redash: setup visual

Now you can save this configuration and add it to a Redash dashboard.

Enjoy!

Apostolos

Project on GitHub