That's really interesting. I've been thinking I'd like to expose the dateutil module`s rrule (recurrence rule) stuff as functions, so maybe that could serve a similar purpose here?

https://dateutil.readthedocs.io/en/stable/rrule.html

SQLite does have a mechanism for creating virtual tables but it's not exposed in the Python sqlite3 module. What IS available is SQLite's JSON module, which can turn a JSON column into a table that can be joined against other results.

So I wonder... if I had a function that returned a JSON list of every date in a given year, would that address these kind of use-cases?

Let's try that out, with a JSON array representing just a single month:

In [2]: days = ['2020-10-{:02d}'.format(i) for i in range(1, 32)]

In [3]: import json

In [4]: print(json.dumps(days))
["2020-10-01", "2020-10-02", "2020-10-03", "2020-10-04", "2020-10-05", "2020-10-06", "2020-10-07", "2020-10-08", "2020-10-09", "2020-10-10", "2020-10-11", "2020-10-12", "2020-10-13", "2020-10-14", "2020-10-15", "2020-10-16", "2020-10-17", "2020-10-18", "2020-10-19", "2020-10-20", "2020-10-21", "2020-10-22", "2020-10-23", "2020-10-24", "2020-10-25", "2020-10-26", "2020-10-27", "2020-10-28", "2020-10-29", "2020-10-30", "2020-10-31"]

select * from json_each('["2020-10-01", "2020-10-02", "2020-10-03", "2020-10-04", "2020-10-05", "2020-10-06", "2020-10-07", "2020-10-08", "2020-10-09", "2020-10-10", "2020-10-11", "2020-10-12", "2020-10-13", "2020-10-14", "2020-10-15", "2020-10-16", "2020-10-17", "2020-10-18", "2020-10-19", "2020-10-20", "2020-10-21", "2020-10-22", "2020-10-23", "2020-10-24", "2020-10-25", "2020-10-26", "2020-10-27", "2020-10-28", "2020-10-29", "2020-10-30", "2020-10-31"]')

https://latest.datasette.io/fixtures?sql=select+*+from+json_each%28%27%5B%222020-10-01%22%2C+%222020-10-02%22%2C+%222020-10-03%22%2C+%222020-10-04%22%2C+%222020-10-05%22%2C+%222020-10-06%22%2C+%222020-10-07%22%2C+%222020-10-08%22%2C+%222020-10-09%22%2C+%222020-10-10%22%2C+%222020-10-11%22%2C+%222020-10-12%22%2C+%222020-10-13%22%2C+%222020-10-14%22%2C+%222020-10-15%22%2C+%222020-10-16%22%2C+%222020-10-17%22%2C+%222020-10-18%22%2C+%222020-10-19%22%2C+%222020-10-20%22%2C+%222020-10-21%22%2C+%222020-10-22%22%2C+%222020-10-23%22%2C+%222020-10-24%22%2C+%222020-10-25%22%2C+%222020-10-26%22%2C+%222020-10-27%22%2C+%222020-10-28%22%2C+%222020-10-29%22%2C+%222020-10-30%22%2C+%222020-10-31%22%5D%27%29

So it looks to me like custom SQL functions that return JSON arrays of date strings could be used in joins!

I think the easiest way to do this would be to expose the rrulestr() function https://dateutil.readthedocs.io/en/stable/rrule.html#rrulestr-examples

I thought about exposing custom SQL functions for each of the different types of recur rules -rrule_daily(...), rrule_monthly(...) etc - but SQLite custom functions need a fixed number of arguments and don't take keyword parameters. This makes it difficult to come up with a clean design for a function where the Python version looks like this: rrule(freq=MONTHLY, count=4, dtstart=start_date)

The nice thing about rrulestr() is that it already provides a standardized mini-language for describing recurrence rules. It would look something like this:

select dateutil_rrulestr("FREQ=DAILY;INTERVAL=10;COUNT=5");
-- returns ["2020-09-28", "2020-10-08", "2020-10-18", "2020-10-28", "2020-11-07"]

Would need to limit the number of records that can come back from this - maybe to 100 or 1000 - otherwise someone could enter a malicious string that exhausts all memory.

I could add some additional SQL functions with nicer syntax for really common cases - like "give me every day between date X and date Y":

select dateutil_dates_between('2020-01-01', '2020-03-01');

This would be inclusive (so you'd get everything from 2020-01-01 to 2020-03-01). Maybe have an optional 3rd argument to specify that you don't want it to be inclusive?

select dateutil_dates_between('2020-01-01', '2020-03-01', 0);

This would return 2020-01-01 up to 2020-02-28.

dateutil_rrulestr() could return full datetimes, dateutil_rrulestr_date() could return just dates.

The documentation will have to show people how to use these with json_each(). Here's a neater example query (it's easier to imagine how you might join this against other tables):

with dates as (
  select
    value
  from
    json_each('["2020-10-01", "2020-10-02", "2020-10-03"]')
)
select
  *
from
  dates;

https://latest.datasette.io/fixtures?sql=with+dates+as+%28%0D%0A++select%0D%0A++++value%0D%0A++from%0D%0A++++json_each%28%27%5B%222020-10-01%22%2C+%222020-10-02%22%2C+%222020-10-03%22%5D%27%29%0D%0A%29%0D%0Aselect%0D%0A++*%0D%0Afrom%0D%0A++dates%3B

With the proposed dateutil_rrulestr() function that would look like this:

with dates as (
  select
    value
  from
    json_each(dateutil_rrulestr("FREQ=DAILY;INTERVAL=10;COUNT=5"))
)
select
  *
from
  dates;

Rather than cutting off at 1000 items I think the functions should raise an exception, because otherwise people may write queries that were truncated at 1000 without realizing it.

A limit of 10,000 values returned in a single JSON list might work fine, and would allow people with even pretty extreme use-cases some head room.

This still needs documentation. I also haven't implemented dateutil_dates_between() yet.

I'm not crazy about this:

select dateutil_rrule('DTSTART:20200101
FREQ=DAILY;INTERVAL=10;COUNT=5')

The DTSTART needs to be on a separate line, and uses : instead of = which is hard to remember.

Instead, how about an optional second argument which you can pass a datetime to - or the result of dateutil_parse?

select dateutil_rrule('FREQ=DAILY;INTERVAL=10;COUNT=5', dateutil_parse('10 october 2020'))

Actually I'll have that second argument always parsed by parser.parse(), so the above example can look like this:

select dateutil_rrule('FREQ=DAILY;INTERVAL=10;COUNT=5', '10 october 2020')

Documentation: https://github.com/simonw/datasette-dateutil/blob/0.2/README.md#json-arrays-of-dates

dateutil_dates_between() demo: 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

dateutil_rrule() demo: https://latest-with-plugins.datasette.io/fixtures?sql=select%0D%0A++dateutil_rrule(%27FREQ%3DHOURLY%3BCOUNT%3D5%27)%2C%0D%0A++dateutil_rrule_date(%0D%0A++++%27FREQ%3DDAILY%3BCOUNT%3D3%27%2C%0D%0A++++%271st+jan+2020%27%0D%0A++)%3B

Added documentation on how to use this with json_each() and a LEFT JOIN here: https://github.com/simonw/datasette-dateutil/blob/0.2.1/README.md#joining-data-using-json_each