Querying for GitHub issues open for less than 60 seconds#

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 closed_at and created_at in seconds. This works:

1
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:

1
duration_open_in_seconds < CAST(:max_duration_in_seconds AS INTEGER)

This is the full query - try it out here:

1
select
2
  json_object(
3
    'label', repos.full_name || ' #' || issues.number,
4
    'href', 'https://github.com/' || repos.full_name || '/issues/' || issues.number
5
  ) as link,
6
  strftime('%s',issues.closed_at) - strftime('%s',issues.created_at) as duration_open_in_seconds,
7
  issues.number as issue_number,
8
  issues.title,
9
  users.login,
10
  issues.closed_at,
11
  issues.created_at,
12
  issues.body,
13
  issues.type
14
from
15
  issues join repos on issues.repo = repos.id
16
  join users on issues.user = users.id
17
  where issues.closed_at is not null and duration_open_in_seconds < CAST(:max_duration_in_seconds AS INTEGER)
18
order by
19
  issues.closed_at desc