3 ms·

Hey HN, I built this project because last year I did a terrible job of actually taking time off work and needed a way to see just how overdue I was to take a da

Hey HN, I built this project because last year I did a terrible job of actually taking time off work and needed a way to see just how overdue I was to take a day off. Because so much is already automatically tracked in my calendar (e.g. the HR system adds calendar events for booked holiday), I just needed a way to query it to get the metrics I wanted. Originally I used a Go MySQL implementation to evaluate queries but it was a bit buggy and didn’t support all the SQL I wanted. Now I’m using a full SQLite database for each user so you can really do some gnarly queries (recursive CTEs, window functions, the works). Personally, I’m using QueryCal as a Grafana datasource to power a dashboard that’s displayed on an old Kindle on my desk (using this great project: https://github.com/pascalw/kindle-dash https://github.com/pascalw/kindle-dash).

mechero 6y ago
Good idea! As a suggestion, I think your website could use some extra documentation and examples. For example, could I create a panel in Grafana showing the busy vs. free ratio over the last three months? That kind of things.
- bradleyjkemp 6y ago
  Absolutely! A starting point would be something like this to calculate total busy time per day: SELECT sum(end - start) from events group by JULIANDAY(start) Some extra aggregation and you could totally calculate your busy/free ratio This is one of the queries I'm using to track a rolling average of how many times a week I've been cycling: WITH recursive dates(day) AS ( SELECT date($__unixepochfrom(), 'unixepoch') UNION ALL SELECT date(day, '+1 DAY') FROM dates WHERE day<date($__unixepochto(), 'unixepoch') ) SELECT day AS time, ( SELECT count(DISTINCT julianday(start)) FROM events WHERE summary = " Cycling" AND start <= date(day, '+1 DAY') AND start > date(day, '-7 DAY')) AS cycling, FROM dates ORDER BY day DESC And yeah 100% admit the documentation is lacking. I'll be honest, I got to the 3 week mark of building and decided I need to validate the idea before putting any more work in. Going to be adding some more pre-done queries like this as inspiration.

mechero 6y ago

Good idea! As a suggestion, I think your website could use some extra documentation and examples. For example, could I create a panel in Grafana showing the busy vs. free ratio over the last three months? That kind of things.

bradleyjkemp 6y ago
Absolutely! A starting point would be something like this to calculate total busy time per day: SELECT sum(end - start) from events group by JULIANDAY(start) Some extra aggregation and you could totally calculate your busy/free ratio This is one of the queries I'm using to track a rolling average of how many times a week I've been cycling: WITH recursive dates(day) AS ( SELECT date($__unixepochfrom(), 'unixepoch') UNION ALL SELECT date(day, '+1 DAY') FROM dates WHERE day<date($__unixepochto(), 'unixepoch') ) SELECT day AS time, ( SELECT count(DISTINCT julianday(start)) FROM events WHERE summary = " Cycling" AND start <= date(day, '+1 DAY') AND start > date(day, '-7 DAY')) AS cycling, FROM dates ORDER BY day DESC And yeah 100% admit the documentation is lacking. I'll be honest, I got to the 3 week mark of building and decided I need to validate the idea before putting any more work in. Going to be adding some more pre-done queries like this as inspiration.