185 words
1 minute
Querying for GitHub issues open for less than 60 seconds
Anubhav Gain
2024-03-06
datasette
/
/
datasette
/
javascript
/
git
/
database
/
web

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
Querying for GitHub issues open for less than 60 seconds
https://mranv.pages.dev/posts/querying-for-github-issues-open-for-less-than-60-seconds/
Author
Anubhav Gain
Published at
2024-03-06
License
CC BY-NC-SA 4.0
© 2026 Anubhav Gain. All Rights Reserved. RSS Sitemap Built with Astro View Source