Show HN: QueryCal – calculate metrics from your calendars using SQL | Hacker News Reader

Show HN: QueryCal – calculate metrics from your calendars using SQL

querycal.com

75 pointsbradleyjkemp13 comments

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).

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.

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.

SQL is a strange, tries-to-look-like-English language.

But the more I experience the document DBs forced down my gullet by the "tech guys" at the startup, the more I appreciate the sheer power of SQL to make things tidy.

My last pet project was basically entirely modeled in a .sql code file (this: https://github.com/asemic-horizon/hyscan/blob/master/prisone...). Very little remains to be written as procedural code.

This is a really neat idea. I usually apply categories to my calendar invites so I can see quickly if I’m spending too much time (or not enough time) toward any type of activity. But I really like your concept since it’s qualitative and truly measurable.

I’m a corporate user and unfortunately locked into Outlook and usually can’t run outside service against my calendar, e.g. calendly. I can occasionally get an Outlook plugin to work though. Have you considered anything like that?

Does your company let you share your calendar privately via an iCal URL? That's the only method of adding your calendar to QueryCal at the moment anyway but is also more likely to work with corporate restrictions (i.e. because it isn't an OAuth app that needs to be approved)

OAuth + calendar plugins are definitely on the roadmap though because it's a much nicer UX than having to dig around in calendar sharing menus

This looks awesome! Seems like a great idea - I'm curious - do you download the full calendar first into a SQLite DB file and then let a user execute queries? SQLite has a "virtual table" feature: https://www3.sqlite.org/vtab.html that could allow "live" queries against the calendar APIs, though my guess is that would be too slow to make sense.

Interesting, hadn't seen the virtual table feature of SQLite. I'd have to do some benchmarking but that could be worth switching to in future for perhaps getting fresher data.

Yes, at the moment there's a batch job to download your calendars and convert them into a SQLite DB. Then, all queries are done directly on that concrete DB (read only).

Originally I was using https://github.com/dolthub/go-mysql-server which is quite similar to the virtual table feature (you just provide a struct which implements some getter methods). Unfortunately I found it a bit slow though (it had to call back into my table many times for even simple queries). Might just be a problem with that implementation and not a limitation of virtual tables themselves so thanks for pointing it out!

I love it and will give it a go!

I'm also intrigued by your 'SQLite-per-user' structure and would be keen to hear how you get on with that long-term. For instance, how will you tackle migrations?

I'm planning on a Stripe-style approach where each user (perhaps even each access token) can choose which version of the database schema they want to query.

I don't have any worries about managing long-term schema migrations though because the per-user databases get blown away and reconstructed every time the calendars are refreshed.

At the moment there's just an `events` table but I might handle migrations by having: `events_v1`, `events_v2`, etc. and just have `events` be an SQL view onto the version you chose.

Managing as little persistent state myself was a specific goal so this project is perfect because apart from some authentication info and a list of iCal URLs, the source of truth for your calendar is always with Google/Microsoft/etc.

Sounds ideal. I really like the isolation of separate DB files per user.

In more complex systems though it feels like it's going to require quite a bit of orchestration.

The big option that SQLite opens up though is portability - I imagine one day just saying to customers that they can download their database and interface with it themselves or shift it to another provider.

Clicking "Demo" on Ubuntu/Firefox 86.0 does nothing :(

It was just a placeholder button I'm afraid :/ I've removed the buttons now to avoid confusion.

I'm planning to have a little SQL client on the homepage you can use to query against demo data (maybe the public holidays calendars from Google?) but I didn't get around to it yet.

It's a bit of a fun challenge locking it down: purposely putting unauthenticated SQLi on your homepage isn't usually recommended!

Reply on news.ycombinator.com