issues-open-for-less-than-x-seconds.md

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:

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