Personal RescueTime (Computer activity time tracker)

I have been a paid user of RescueTime every month since June 2015, but since I love collecting and analyzing data, I decided to replicate the functionality and keep track of the data in my own local database. Using applescript and postgresql, I've been recording computer usage (application name and window title) since "2017-12-08 20:06:00.670021". I was just completing a survey on Coursera, which is asking "How many hours per week do you plan to spend working on this course?", and because I started recording data on December 8th and started taking the class on the 6th, I knew I had data for the majority of my coursera time. This doesn't account for course-related material that wasn't on coursera's website or didn't have "Coursera" in the title, and now I'm also realizing I should try to grab a url of the current application I'm in, if I can (unfortunately, some quick google searches don't return anything obvious for firefox, although chrome does have it built in..).

with base as (
    select
            *
            , datetime-lag(datetime) over (partition by true order by datetime) as time_between
            , extract(year from datetime)::text||'-'||extract(week from datetime)::text as week
        from user_activities
        where (window_name ilike '%coursera%')
            or (application ilike '%firefox%' and (
                window_name ilike '%week+%'
                or window_name ilike '%assignment%'))
        order by datetime
    ) , filtered as (
        select *
        from base
        where time_between

week   | time_spent_on_coursera
---------+------------------------
 2017-49 | 00:48:47.559574
 2017-50 | 09:45:30.929945
 2017-51 | 04:02:11.548172
(3 rows)

Note that for week 2017-50, that's 9 hours and 45 minutes. If anyone is interested, I could come back and explain this a bit. It's not completely accurate, because Coursera refers to some materials that don't have "coursera" in the title, and I could have been busy doing something non-coursera-related for less than 15 minutes, which would make it less accurate.

I recommend RescueTime, as it is an awesome tool for finding out a quantitative truth about where you spend your time on the computer: https://www.rescuetime.com