While writing this thread about my habit of opening issues and closing them a few seconds later just so I could link to them in a commit message I decided to answer the question "How many of my issues were open for less than 60 seconds?"
Thanks to github-to-sqlite I have an issues database table containing issues from all of my public projects.
I needed to figure out how to calculate the difference between
created_at in seconds. This works:
select strftime('%s',issues.closed_at) - strftime('%s',issues.created_at) as duration_open_in_seconds ...
I wanted to be able to input the number of seconds as a parameter. I used this:
duration_open_in_seconds < CAST(:max_duration_in_seconds AS INTEGER)
This is the full query - try it out here:
select json_object( 'label', repos.full_name || ' #' || issues.number, 'href', 'https://github.com/' || repos.full_name || '/issues/' || issues.number ) as link, strftime('%s',issues.closed_at) - strftime('%s',issues.created_at) as duration_open_in_seconds, issues.number as issue_number, issues.title, users.login, issues.closed_at, issues.created_at, issues.body, issues.type from issues join repos on issues.repo = repos.id join users on issues.user = users.id where issues.closed_at is not null and duration_open_in_seconds < CAST(:max_duration_in_seconds AS INTEGER) order by issues.closed_at desc
Created 2021-03-12T07:34:42-08:00 · Edit