til: datasette_issues-open-for-less-than-x-seconds.md
This data as json
| path | topic | title | url | body | html | shot | created | created_utc | updated | updated_utc | shot_hash | slug |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| datasette_issues-open-for-less-than-x-seconds.md | datasette | Querying for GitHub issues open for less than 60 seconds | https://github.com/simonw/til/blob/main/datasette/issues-open-for-less-than-x-seconds.md | While [writing this thread](https://twitter.com/simonw/status/1370390336514658310) 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](https://datasette.io/tools/github-to-sqlite) I have an [issues database table](https://github-to-sqlite.dogsheep.net/github/issues) 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: ```sql 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: ```sql duration_open_in_seconds < CAST(:max_duration_in_seconds AS INTEGER) ``` This is the full query - [try it out here](https://github-to-sqlite.dogsheep.net/github?sql=select%0D%0A++json_object%28%0D%0A++++%27label%27%2C+repos.full_name+%7C%7C+%27+%23%27+%7C%7C+issues.number%2C%0D%0A++++%27href%27%2C+%27https%3A%2F%2Fgithub.com%2F%27+%7C%7C+repos.full_name+%7C%7C+%27%2Fissues%2F%27+%7C%7C+issues.number%0D%0A++%29+as+link%2C%0D%0A++strftime%28%27%25s%27%2Cissues.closed_at%29+-+strftime%28%27%25s%27%2Cissues.created_at%29+as+duration_open_in_seconds%2C%0D%0A++issues.number+as+issue_number%2C%0D%0A++issues.title%2C%0D%0A++users.login%2C%0D%0A++issues.closed_at%2C%0D%0A++issues.created_at%2C%0D%0A++issues.body%2C%0D%0A++issues.type%0D%0Afrom%0D%0A++issues+join+repos+on+issues.repo+%3D+repos.id%0D%0A++join+users+on+issues.user+%3D+users.id%0D%0A++where+issues.closed_at+is+not+null+and+duration_open_in_seconds+%3C+CAST%28%3Amax_duration_in_seconds+AS+INTEGER%29%0D%0Aorder+by%0D%0A++issues.closed_at+desc&max_duration_in_seconds=60): ```sql 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 ``` | <p>While <a href="https://twitter.com/simonw/status/1370390336514658310" rel="nofollow">writing this thread</a> 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?"</p> <p>Thanks to <a href="https://datasette.io/tools/github-to-sqlite" rel="nofollow">github-to-sqlite</a> I have an <a href="https://github-to-sqlite.dogsheep.net/github/issues" rel="nofollow">issues database table</a> containing issues from all of my public projects.</p> <p>I needed to figure out how to calculate the difference between <code>closed_at</code> and <code>created_at</code> in seconds. This works:</p> <div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> strftime(<span class="pl-s"><span class="pl-pds">'</span>%s<span class="pl-pds">'</span></span>,<span class="pl-c1">issues</span>.<span class="pl-c1">closed_at</span>) <span class="pl-k">-</span> strftime(<span class="pl-s"><span class="pl-pds">'</span>%s<span class="pl-pds">'</span></span>,<span class="pl-c1">issues</span>.<span class="pl-c1">created_at</span>) <span class="pl-k">as</span> duration_open_in_seconds ...</pre></div> <p>I wanted to be able to input the number of seconds as a parameter. I used this:</p> <div class="highlight highlight-source-sql"><pre>duration_open_in_seconds <span class="pl-k"><</span> CAST(:max_duration_in_seconds <span class="pl-k">AS</span> <span class="pl-k">INTEGER</span>)</pre></div> <p>This is the full query - <a href="https://github-to-sqlite.dogsheep.net/github?sql=select%0D%0A++json_object%28%0D%0A++++%27label%27%2C+repos.full_name+%7C%7C+%27+%23%27+%7C%7C+issues.number%2C%0D%0A++++%27href%27%2C+%27https%3A%2F%2Fgithub.com%2F%27+%7C%7C+repos.full_name+%7C%7C+%27%2Fissues%2F%27+%7C%7C+issues.number%0D%0A++%29+as+link%2C%0D%0A++strftime%28%27%25s%27%2Cissues.closed_at%29+-+strftime%28%27%25s%27%2Cissues.created_at%29+as+duration_open_in_seconds%2C%0D%0A++issues.number+as+issue_number%2C%0D%0A++issues.title%2C%0D%0A++users.login%2C%0D%0A++issues.closed_at%2C%0D%0A++issues.created_at%2C%0D%0A++issues.body%2C%0D%0A++issues.type%0D%0Afrom%0D%0A++issues+join+repos+on+issues.repo+%3D+repos.id%0D%0A++join+users+on+issues.user+%3D+users.id%0D%0A++where+issues.closed_at+is+not+null+and+duration_open_in_seconds+%3C+CAST%28%3Amax_duration_in_seconds+AS+INTEGER%29%0D%0Aorder+by%0D%0A++issues.closed_at+desc&max_duration_in_seconds=60" rel="nofollow">try it out here</a>:</p> <div class="highlight highlight-source-sql"><pre><span class="pl-k">select</span> json_object( <span class="pl-s"><span class="pl-pds">'</span>label<span class="pl-pds">'</span></span>, <span class="pl-c1">repos</span>.<span class="pl-c1">full_name</span> <span class="pl-k">||</span> <span class="pl-s"><span class="pl-pds">'</span> #<span class="pl-pds">'</span></span> <span class="pl-k">||</span> <span class="pl-c1">issues</span>.<span class="pl-c1">number</span>, <span class="pl-s"><span class="pl-pds">'</span>href<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>https://github.com/<span class="pl-pds">'</span></span> <span class="pl-k">||</span> <span class="pl-c1">repos</span>.<span class="pl-c1">full_name</span> <span class="pl-k">||</span> <span class="pl-s"><span class="pl-pds">'</span>/issues/<span class="pl-pds">'</span></span> <span class="pl-k">||</span> <span class="pl-c1">issues</span>.<span class="pl-c1">number</span> ) <span class="pl-k">as</span> link, strftime(<span class="pl-s"><span class="pl-pds">'</span>%s<span class="pl-pds">'</span></span>,<span class="pl-c1">issues</span>.<span class="pl-c1">closed_at</span>) <span class="pl-k">-</span> strftime(<span class="pl-s"><span class="pl-pds">'</span>%s<span class="pl-pds">'</span></span>,<span class="pl-c1">issues</span>.<span class="pl-c1">created_at</span>) <span class="pl-k">as</span> duration_open_in_seconds, <span class="pl-c1">issues</span>.<span class="pl-c1">number</span> <span class="pl-k">as</span> issue_number, <span class="pl-c1">issues</span>.<span class="pl-c1">title</span>, <span class="pl-c1">users</span>.<span class="pl-c1">login</span>, <span class="pl-c1">issues</span>.<span class="pl-c1">closed_at</span>, <span class="pl-c1">issues</span>.<span class="pl-c1">created_at</span>, <span class="pl-c1">issues</span>.<span class="pl-c1">body</span>, <span class="pl-c1">issues</span>.<span class="pl-c1">type</span> <span class="pl-k">from</span> issues <span class="pl-k">join</span> repos <span class="pl-k">on</span> <span class="pl-c1">issues</span>.<span class="pl-c1">repo</span> <span class="pl-k">=</span> <span class="pl-c1">repos</span>.<span class="pl-c1">id</span> <span class="pl-k">join</span> users <span class="pl-k">on</span> <span class="pl-c1">issues</span>.<span class="pl-c1">user</span> <span class="pl-k">=</span> <span class="pl-c1">users</span>.<span class="pl-c1">id</span> <span class="pl-k">where</span> <span class="pl-c1">issues</span>.<span class="pl-c1">closed_at</span> <span class="pl-k">is not null</span> <span class="pl-k">and</span> duration_open_in_seconds <span class="pl-k"><</span> CAST(:max_duration_in_seconds <span class="pl-k">AS</span> <span class="pl-k">INTEGER</span>) <span class="pl-k">order by</span> <span class="pl-c1">issues</span>.<span class="pl-c1">closed_at</span> <span class="pl-k">desc</span></pre></div> | <Binary: 72,270 bytes> | 2021-03-12T07:34:42-08:00 | 2021-03-12T15:34:42+00:00 | 2021-03-12T07:34:42-08:00 | 2021-03-12T15:34:42+00:00 | e0f132933840169839c18ddd06e78cac | issues-open-for-less-than-x-seconds |