repos: 299143849
This data as json
| id | node_id | name | full_name | private | owner | html_url | description | fork | created_at | updated_at | pushed_at | homepage | size | stargazers_count | watchers_count | language | has_issues | has_projects | has_downloads | has_wiki | has_pages | forks_count | archived | disabled | open_issues_count | license | topics | forks | open_issues | watchers | default_branch | permissions | temp_clone_token | organization | network_count | subscribers_count | readme | readme_html | allow_forking | visibility | is_template | template_repository | web_commit_signoff_required | has_discussions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 299143849 | MDEwOlJlcG9zaXRvcnkyOTkxNDM4NDk= | datasette-dateutil | simonw/datasette-dateutil | 0 | 9599 | https://github.com/simonw/datasette-dateutil | dateutil functions for Datasette | 0 | 2020-09-28T00:14:20Z | 2022-03-01T00:09:57Z | 2022-03-01T01:40:21Z | 18 | 6 | 6 | Python | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 2 | ["datasette", "datasette-io", "datasette-plugin", "dateutil"] | 0 | 2 | 6 | main | {"admin": false, "maintain": false, "push": false, "triage": false, "pull": false} | 0 | 2 | # datasette-dateutil [](https://pypi.org/project/datasette-dateutil/) [](https://github.com/simonw/datasette-dateutil/releases) [](https://github.com/simonw/datasette-dateutil/actions?query=workflow%3ATest) [](https://github.com/simonw/datasette-dateutil/blob/main/LICENSE) dateutil functions for Datasette ## Installation Install this plugin in the same environment as Datasette. $ datasette install datasette-dateutil ## Usage This function adds custom SQL functions that expose functionality from the [dateutil](https://dateutil.readthedocs.io/) Python library. Once installed, the following SQL functions become available: ### Parsing date strings - `dateutil_parse(text)` - returns an ISO8601 date string parsed from the text, or `null` if the input could not be parsed. `dateutil_parse("10 october 2020 3pm")` returns `2020-10-10T15:00:00`. - `dateutil_parse_fuzzy(text)` - same as `dateutil_parse()` but this also works against strings that contain a date somewhere within them - that date will be returned, or `null` if no dates could be found. `dateutil_parse_fuzzy("This is due 10 september")` returns `2020-09-10T00:00:00` (but will start returning the 2021 version of that if the year is 2021). The `dateutil_parse()` and `dateutil_parse_fuzzy()` functions both follow the American convention of assuming that `1/2/2020` lists the month first, evaluating this example to the 2nd of January. If you want to assume that the day comes first, use these two functions instead: - `dateutil_parse_dayfirst(text)` - `dateutil_parse_fuzzy_dayfirst(text)` Here's a query demonstrating these functions: ```sql select dateutil_parse("10 october 2020 3pm"), dateutil_parse_fuzzy("This is due 10 september"), dateutil_parse("1/2/2020"), dateutil_parse("2020-03-04"), dateutil_parse_dayfirst("2020-03-04"); ``` [Try that query](https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_parse%28%2210+october+2020+3pm%22%29%2C%0D%0A++dateutil_parse_fuzzy%28%22This+is+due+10+september%22%29%2C%0D%0A++dateutil_parse%28%221%2F2%2F2020%22%29%2C%0D%0A++dateutil_parse%28%222020-03-04%22%29%2C%0D%0A++dateutil_parse_dayfirst%28%222020-03-04%22%29%3B) ### Optional default dates The `dateutil_parse()`, `dateutil_parse_fuzzy()`, `dateutil_parse_dayfirst()` and `dateutil_parse_fuzzy_dayfirst()` functions all accept an optional second argument specifying a "default" datetime to consider if some of the details are missing. For example, the following: ```sql select dateutil_parse('1st october', '1985-01-01') ``` Will return `1985-10-01T00:00:00` - the missing year is replaced with the year from the default date. [Example query demonstrating the default date argument](https://latest-with-plugins.datasette.io/fixtures?sql=with+times+as+%28%0D%0A++select%0D%0A++++datetime%28%27now%27%29+as+t%0D%0A++union%0D%0A++select%0D%0A++++datetime%28%27now%27%2C+%27-1+year%27%29%0D%0A++union%0D%0A++select%0D%0A++++datetime%28%27now%27%2C+%27-3+years%27%29%0D%0A%29%0D%0Aselect+t%2C+dateutil_parse_fuzzy%28%22This+is+due+10+september%22%2C+t%29+from+times) ### Calculating Easter - `dateutil_easter(year)` - returns the date for Easter in that year, for example `dateutil_easter("2020")` returns `2020-04-12`. [Example Easter query](https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_easter%282019%29%2C%0D%0A++dateutil_easter%282020%29%2C%0D%0A++dateutil_easter%282021%29) ### JSON arrays of dates Several functions return JSON arrays of date strings. These can be used with SQLite's `json_each()` function to perform joins against dates from a specific date range or recurrence rule. These functions can return up to 10,000 results. They will return an error if more than 10,000 dates would be returned - this is to protect against denial of service attacks. - `dateutil_dates_between('1 january 2020', '5 jan 2020')` - given two dates (in any format that can be handled by `dateutil_parse()`) this function returns a JSON string containing the dates between those two days, inclusive. This example returns `["2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04", "2020-01-05"]`. - `dateutil_dates_between('1 january 2020', '5 jan 2020', 0)` - set the optional third argument to `0` to specify that you would like this to be exclusive of the last day. This example returns `["2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04"]`. [Try these queries](https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_dates_between%28%271+january+2020%27%2C+%275+jan+2020%27%29%2C%0D%0A++dateutil_dates_between%28%271+january+2020%27%2C+%275+jan+2020%27%2C+0%29) The `dateutil_rrule()` and `dateutil_rrule_date()` functions accept the iCalendar standard ``rrule` format - see [the dateutil documentation](https://dateutil.readthedocs.io/en/stable/rrule.html#rrulestr-examples) for more examples. This format lets you specify recurrence rules such as "the next four last mondays of the month". - `dateutil_rrule(rrule, optional_dtsart)` - given an rrule returns a JSON array of ISO datetimes. The second argument is optional and will be treated as the start date for the rule. - `dateutil_rrule_date(rrule, optional_dtsart)` - same as `dateutil_rrule()` but returns ISO dates. Example query: ```sql select dateutil_rrule('FREQ=HOURLY;COUNT=5'), dateutil_rrule_date( 'FREQ=DAILY;COUNT=3', '1st jan 2020' ); ``` [Try the rrule example query](https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_rrule('FREQ%3DHOURLY%3BCOUNT%3D5')%2C%0D%0A++dateutil_rrule_date(%0D%0A++++'FREQ%3DDAILY%3BCOUNT%3D3'%2C%0D%0A++++'1st+jan+2020'%0D%0A++)%3B) ### Joining data using json_each() SQLite's [json_each() function](https://www.sqlite.org/json1.html#jeach) can be used to turn a JSON array of dates into a table that can be joined against other data. Here's a query that returns a table showing every day in January 2019: ```sql select value as date from json_each( dateutil_dates_between('1 Jan 2019', '31 Jan 2019') ) ``` [Try that query](https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++value+as+date%0D%0Afrom%0D%0A++json_each%28%0D%0A++++dateutil_dates_between%28%271+Jan+2019%27%2C+%2731+Jan+2019%27%29%0D%0A++%29) You can run joins against this table by assigning it a name using SQLite's [support for Common Table Expressions (CTEs)](https://sqlite.org/lang_with.html). This example query uses `substr(created, 0, 11)` to retrieve the date portion of the `created` column in the [facetable demo table](https://latest-with-plugins.datasette.io/fixtures/facetable), then joins that against the table of days in January to calculate the count of rows created on each day. The `LEFT JOIN` against `days_in_january` ensures that days which had no created records are still returned in the results, with a count of 0. ```sql with created_dates as ( select substr(created, 0, 11) as date from facetable ), days_in_january as ( select value as date from json_each( dateutil_dates_between('1 Jan 2019', '31 Jan 2019') ) ) select days_in_january.date, count(created_dates.date) as total from days_in_january left join created_dates on days_in_january.date = created_dates.date group by days_in_january.date; ``` [Try that query](https://latest-with-plugins.datasette.io/fixtures?sql=with+created_dates+as+%28%0D%0A++select%0D%0A++++substr%28created%2C+0%2C+11%29+as+date%0D%0A++from%0D%0A++++facetable%0D%0A%29%2C%0D%0Adays_in_january+as+%28%0D%0A++select%0D%0A++++value+as+date%0D%0A++from%0D%0A++++json_each%28%0D%0A++++++dateutil_dates_between%28%271+Jan+2019%27%2C+%2731+Jan+2019%27%29%0D%0A++++%29%0D%0A%29%0D%0Aselect%0D%0A++days_in_january.date%2C%0D%0A++count%28created_dates.date%29+as+total%0D%0Afrom%0D%0A++days_in_january%0D%0A++left+join+created_dates+on+days_in_january.date+%3D+created_dates.date%0D%0Agroup+by%0D%0A++days_in_january.date%3B#g.mark=bar&g.x_column=date&g.x_type=ordinal&g.y_column=total&g.y_type=quantitative) with a bar chart rendered using the [datasette-vega](https://github.com/simonw/datasette-vega) plugin. ## Development To set up this plugin locally, first checkout the code. Then create a new virtual environment: cd datasette-dateutil python3 -mvenv venv source venv/bin/activate Or if you are using `pipenv`: pipenv shell Now install the dependencies and tests: pip install -e '.[test]' To run the tests: pytest | <div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1 dir="auto"><a id="user-content-datasette-dateutil" class="anchor" aria-hidden="true" href="#user-content-datasette-dateutil"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>datasette-dateutil</h1> <p dir="auto"><a href="https://pypi.org/project/datasette-dateutil/" rel="nofollow"><img src="https://camo.githubusercontent.com/0e859dccf125298272af85fddd25900a6c9a11a9ebba16d8cc93782759d53e21/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f6461746173657474652d646174657574696c2e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/datasette-dateutil.svg" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-dateutil/releases"><img src="https://camo.githubusercontent.com/084ae9754f14a59ff18c2c74d621cab421866c6af7f4c99c9c94588cdf6cac2a/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f72656c656173652f73696d6f6e772f6461746173657474652d646174657574696c3f696e636c7564655f70726572656c6561736573266c6162656c3d6368616e67656c6f67" alt="Changelog" data-canonical-src="https://img.shields.io/github/v/release/simonw/datasette-dateutil?include_prereleases&label=changelog" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-dateutil/actions?query=workflow%3ATest"><img src="https://github.com/simonw/datasette-dateutil/workflows/Test/badge.svg" alt="Tests" style="max-width: 100%;"></a> <a href="https://github.com/simonw/datasette-dateutil/blob/main/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width: 100%;"></a></p> <p dir="auto">dateutil functions for Datasette</p> <h2 dir="auto"><a id="user-content-installation" class="anchor" aria-hidden="true" href="#user-content-installation"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Installation</h2> <p dir="auto">Install this plugin in the same environment as Datasette.</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="$ datasette install datasette-dateutil"><pre><code>$ datasette install datasette-dateutil </code></pre></div> <h2 dir="auto"><a id="user-content-usage" class="anchor" aria-hidden="true" href="#user-content-usage"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Usage</h2> <p dir="auto">This function adds custom SQL functions that expose functionality from the <a href="https://dateutil.readthedocs.io/" rel="nofollow">dateutil</a> Python library.</p> <p dir="auto">Once installed, the following SQL functions become available:</p> <h3 dir="auto"><a id="user-content-parsing-date-strings" class="anchor" aria-hidden="true" href="#user-content-parsing-date-strings"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Parsing date strings</h3> <ul dir="auto"> <li><code>dateutil_parse(text)</code> - returns an ISO8601 date string parsed from the text, or <code>null</code> if the input could not be parsed. <code>dateutil_parse("10 october 2020 3pm")</code> returns <code>2020-10-10T15:00:00</code>.</li> <li><code>dateutil_parse_fuzzy(text)</code> - same as <code>dateutil_parse()</code> but this also works against strings that contain a date somewhere within them - that date will be returned, or <code>null</code> if no dates could be found. <code>dateutil_parse_fuzzy("This is due 10 september")</code> returns <code>2020-09-10T00:00:00</code> (but will start returning the 2021 version of that if the year is 2021).</li> </ul> <p dir="auto">The <code>dateutil_parse()</code> and <code>dateutil_parse_fuzzy()</code> functions both follow the American convention of assuming that <code>1/2/2020</code> lists the month first, evaluating this example to the 2nd of January.</p> <p dir="auto">If you want to assume that the day comes first, use these two functions instead:</p> <ul dir="auto"> <li><code>dateutil_parse_dayfirst(text)</code></li> <li><code>dateutil_parse_fuzzy_dayfirst(text)</code></li> </ul> <p dir="auto">Here's a query demonstrating these functions:</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="select dateutil_parse("10 october 2020 3pm"), dateutil_parse_fuzzy("This is due 10 september"), dateutil_parse("1/2/2020"), dateutil_parse("2020-03-04"), dateutil_parse_dayfirst("2020-03-04");"><pre><span class="pl-k">select</span> dateutil_parse(<span class="pl-s"><span class="pl-pds">"</span>10 october 2020 3pm<span class="pl-pds">"</span></span>), dateutil_parse_fuzzy(<span class="pl-s"><span class="pl-pds">"</span>This is due 10 september<span class="pl-pds">"</span></span>), dateutil_parse(<span class="pl-s"><span class="pl-pds">"</span>1/2/2020<span class="pl-pds">"</span></span>), dateutil_parse(<span class="pl-s"><span class="pl-pds">"</span>2020-03-04<span class="pl-pds">"</span></span>), dateutil_parse_dayfirst(<span class="pl-s"><span class="pl-pds">"</span>2020-03-04<span class="pl-pds">"</span></span>);</pre></div> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_parse%28%2210+october+2020+3pm%22%29%2C%0D%0A++dateutil_parse_fuzzy%28%22This+is+due+10+september%22%29%2C%0D%0A++dateutil_parse%28%221%2F2%2F2020%22%29%2C%0D%0A++dateutil_parse%28%222020-03-04%22%29%2C%0D%0A++dateutil_parse_dayfirst%28%222020-03-04%22%29%3B" rel="nofollow">Try that query</a></p> <h3 dir="auto"><a id="user-content-optional-default-dates" class="anchor" aria-hidden="true" href="#user-content-optional-default-dates"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Optional default dates</h3> <p dir="auto">The <code>dateutil_parse()</code>, <code>dateutil_parse_fuzzy()</code>, <code>dateutil_parse_dayfirst()</code> and <code>dateutil_parse_fuzzy_dayfirst()</code> functions all accept an optional second argument specifying a "default" datetime to consider if some of the details are missing. For example, the following:</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="select dateutil_parse('1st october', '1985-01-01')"><pre><span class="pl-k">select</span> dateutil_parse(<span class="pl-s"><span class="pl-pds">'</span>1st october<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>1985-01-01<span class="pl-pds">'</span></span>)</pre></div> <p dir="auto">Will return <code>1985-10-01T00:00:00</code> - the missing year is replaced with the year from the default date.</p> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=with+times+as+%28%0D%0A++select%0D%0A++++datetime%28%27now%27%29+as+t%0D%0A++union%0D%0A++select%0D%0A++++datetime%28%27now%27%2C+%27-1+year%27%29%0D%0A++union%0D%0A++select%0D%0A++++datetime%28%27now%27%2C+%27-3+years%27%29%0D%0A%29%0D%0Aselect+t%2C+dateutil_parse_fuzzy%28%22This+is+due+10+september%22%2C+t%29+from+times" rel="nofollow">Example query demonstrating the default date argument</a></p> <h3 dir="auto"><a id="user-content-calculating-easter" class="anchor" aria-hidden="true" href="#user-content-calculating-easter"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Calculating Easter</h3> <ul dir="auto"> <li><code>dateutil_easter(year)</code> - returns the date for Easter in that year, for example <code>dateutil_easter("2020")</code> returns <code>2020-04-12</code>.</li> </ul> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_easter%282019%29%2C%0D%0A++dateutil_easter%282020%29%2C%0D%0A++dateutil_easter%282021%29" rel="nofollow">Example Easter query</a></p> <h3 dir="auto"><a id="user-content-json-arrays-of-dates" class="anchor" aria-hidden="true" href="#user-content-json-arrays-of-dates"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>JSON arrays of dates</h3> <p dir="auto">Several functions return JSON arrays of date strings. These can be used with SQLite's <code>json_each()</code> function to perform joins against dates from a specific date range or recurrence rule.</p> <p dir="auto">These functions can return up to 10,000 results. They will return an error if more than 10,000 dates would be returned - this is to protect against denial of service attacks.</p> <ul dir="auto"> <li><code>dateutil_dates_between('1 january 2020', '5 jan 2020')</code> - given two dates (in any format that can be handled by <code>dateutil_parse()</code>) this function returns a JSON string containing the dates between those two days, inclusive. This example returns <code>["2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04", "2020-01-05"]</code>.</li> <li><code>dateutil_dates_between('1 january 2020', '5 jan 2020', 0)</code> - set the optional third argument to <code>0</code> to specify that you would like this to be exclusive of the last day. This example returns <code>["2020-01-01", "2020-01-02", "2020-01-03", "2020-01-04"]</code>.</li> </ul> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_dates_between%28%271+january+2020%27%2C+%275+jan+2020%27%29%2C%0D%0A++dateutil_dates_between%28%271+january+2020%27%2C+%275+jan+2020%27%2C+0%29" rel="nofollow">Try these queries</a></p> <p dir="auto">The <code>dateutil_rrule()</code> and <code>dateutil_rrule_date()</code> functions accept the iCalendar standard ``rrule` format - see <a href="https://dateutil.readthedocs.io/en/stable/rrule.html#rrulestr-examples" rel="nofollow">the dateutil documentation</a> for more examples.</p> <p dir="auto">This format lets you specify recurrence rules such as "the next four last mondays of the month".</p> <ul dir="auto"> <li><code>dateutil_rrule(rrule, optional_dtsart)</code> - given an rrule returns a JSON array of ISO datetimes. The second argument is optional and will be treated as the start date for the rule.</li> <li><code>dateutil_rrule_date(rrule, optional_dtsart)</code> - same as <code>dateutil_rrule()</code> but returns ISO dates.</li> </ul> <p dir="auto">Example query:</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="select dateutil_rrule('FREQ=HOURLY;COUNT=5'), dateutil_rrule_date( 'FREQ=DAILY;COUNT=3', '1st jan 2020' );"><pre><span class="pl-k">select</span> dateutil_rrule(<span class="pl-s"><span class="pl-pds">'</span>FREQ=HOURLY;COUNT=5<span class="pl-pds">'</span></span>), dateutil_rrule_date( <span class="pl-s"><span class="pl-pds">'</span>FREQ=DAILY;COUNT=3<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>1st jan 2020<span class="pl-pds">'</span></span> );</pre></div> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_rrule('FREQ%3DHOURLY%3BCOUNT%3D5')%2C%0D%0A++dateutil_rrule_date(%0D%0A++++'FREQ%3DDAILY%3BCOUNT%3D3'%2C%0D%0A++++'1st+jan+2020'%0D%0A++)%3B" rel="nofollow">Try the rrule example query</a></p> <h3 dir="auto"><a id="user-content-joining-data-using-json_each" class="anchor" aria-hidden="true" href="#user-content-joining-data-using-json_each"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Joining data using json_each()</h3> <p dir="auto">SQLite's <a href="https://www.sqlite.org/json1.html#jeach" rel="nofollow">json_each() function</a> can be used to turn a JSON array of dates into a table that can be joined against other data. Here's a query that returns a table showing every day in January 2019:</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="select value as date from json_each( dateutil_dates_between('1 Jan 2019', '31 Jan 2019') )"><pre><span class="pl-k">select</span> value <span class="pl-k">as</span> <span class="pl-k">date</span> <span class="pl-k">from</span> json_each( dateutil_dates_between(<span class="pl-s"><span class="pl-pds">'</span>1 Jan 2019<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>31 Jan 2019<span class="pl-pds">'</span></span>) )</pre></div> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++value+as+date%0D%0Afrom%0D%0A++json_each%28%0D%0A++++dateutil_dates_between%28%271+Jan+2019%27%2C+%2731+Jan+2019%27%29%0D%0A++%29" rel="nofollow">Try that query</a></p> <p dir="auto">You can run joins against this table by assigning it a name using SQLite's <a href="https://sqlite.org/lang_with.html" rel="nofollow">support for Common Table Expressions (CTEs)</a>.</p> <p dir="auto">This example query uses <code>substr(created, 0, 11)</code> to retrieve the date portion of the <code>created</code> column in the <a href="https://latest-with-plugins.datasette.io/fixtures/facetable" rel="nofollow">facetable demo table</a>, then joins that against the table of days in January to calculate the count of rows created on each day. The <code>LEFT JOIN</code> against <code>days_in_january</code> ensures that days which had no created records are still returned in the results, with a count of 0.</p> <div class="highlight highlight-source-sql position-relative overflow-auto" data-snippet-clipboard-copy-content="with created_dates as ( select substr(created, 0, 11) as date from facetable ), days_in_january as ( select value as date from json_each( dateutil_dates_between('1 Jan 2019', '31 Jan 2019') ) ) select days_in_january.date, count(created_dates.date) as total from days_in_january left join created_dates on days_in_january.date = created_dates.date group by days_in_january.date;"><pre>with created_dates <span class="pl-k">as</span> ( <span class="pl-k">select</span> substr(created, <span class="pl-c1">0</span>, <span class="pl-c1">11</span>) <span class="pl-k">as</span> <span class="pl-k">date</span> <span class="pl-k">from</span> facetable ), days_in_january <span class="pl-k">as</span> ( <span class="pl-k">select</span> value <span class="pl-k">as</span> <span class="pl-k">date</span> <span class="pl-k">from</span> json_each( dateutil_dates_between(<span class="pl-s"><span class="pl-pds">'</span>1 Jan 2019<span class="pl-pds">'</span></span>, <span class="pl-s"><span class="pl-pds">'</span>31 Jan 2019<span class="pl-pds">'</span></span>) ) ) <span class="pl-k">select</span> <span class="pl-c1">days_in_january</span>.<span class="pl-c1">date</span>, <span class="pl-c1">count</span>(<span class="pl-c1">created_dates</span>.<span class="pl-c1">date</span>) <span class="pl-k">as</span> total <span class="pl-k">from</span> days_in_january <span class="pl-k">left join</span> created_dates <span class="pl-k">on</span> <span class="pl-c1">days_in_january</span>.<span class="pl-c1">date</span> <span class="pl-k">=</span> <span class="pl-c1">created_dates</span>.<span class="pl-c1">date</span> <span class="pl-k">group by</span> <span class="pl-c1">days_in_january</span>.<span class="pl-c1">date</span>;</pre></div> <p dir="auto"><a href="https://latest-with-plugins.datasette.io/fixtures?sql=with+created_dates+as+%28%0D%0A++select%0D%0A++++substr%28created%2C+0%2C+11%29+as+date%0D%0A++from%0D%0A++++facetable%0D%0A%29%2C%0D%0Adays_in_january+as+%28%0D%0A++select%0D%0A++++value+as+date%0D%0A++from%0D%0A++++json_each%28%0D%0A++++++dateutil_dates_between%28%271+Jan+2019%27%2C+%2731+Jan+2019%27%29%0D%0A++++%29%0D%0A%29%0D%0Aselect%0D%0A++days_in_january.date%2C%0D%0A++count%28created_dates.date%29+as+total%0D%0Afrom%0D%0A++days_in_january%0D%0A++left+join+created_dates+on+days_in_january.date+%3D+created_dates.date%0D%0Agroup+by%0D%0A++days_in_january.date%3B#g.mark=bar&g.x_column=date&g.x_type=ordinal&g.y_column=total&g.y_type=quantitative" rel="nofollow">Try that query</a> with a bar chart rendered using the <a href="https://github.com/simonw/datasette-vega">datasette-vega</a> plugin.</p> <h2 dir="auto"><a id="user-content-development" class="anchor" aria-hidden="true" href="#user-content-development"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Development</h2> <p dir="auto">To set up this plugin locally, first checkout the code. Then create a new virtual environment:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="cd datasette-dateutil python3 -mvenv venv source venv/bin/activate"><pre><code>cd datasette-dateutil python3 -mvenv venv source venv/bin/activate </code></pre></div> <p dir="auto">Or if you are using <code>pipenv</code>:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="pipenv shell"><pre><code>pipenv shell </code></pre></div> <p dir="auto">Now install the dependencies and tests:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="pip install -e '.[test]'"><pre><code>pip install -e '.[test]' </code></pre></div> <p dir="auto">To run the tests:</p> <div class="snippet-clipboard-content position-relative overflow-auto" data-snippet-clipboard-copy-content="pytest"><pre><code>pytest </code></pre></div> </article></div> | 1 | public | 0 |
Links from other tables
- 4 rows from repo in releases