date, year, month and day querystring lookups

[simonw]

• ?timestamp___date=2017-07-17 - return every item where the timestamp falls on that date
• ?timestamp___year=2017 - return every item where the timestamp falls within 2017
• ?timestamp___month=1 - return every item where the month component is January
• ?timestamp___day=10 - return every item where the day-of-the-month component is 10

Follow on from #23

[simonw]

Consider for example https://fivethirtyeight.datasettes.com/fivethirtyeight/inconvenient-sequel%2Fratings

The idea here is to be able to support querystring parameters like this:

• ?timestamp___date=2017-07-17 - return every item where the timestamp falls on that date
• ?timestamp___year=2017 - return every item where the timestamp falls within 2017
• ?timestamp___month=1 - return every item where the month component is January
• ?timestamp___day=10 - return every item where the day-of-the-month component is 10

This is similar to #64 but a fair bit more complicated.

SQLite date functions are documented here: https://sqlite.org/lang_datefunc.html

[simonw]

This may be useful:

https://github.com/coleifer/peewee/blob/db85167d93861451a1fe7cde8c4f05748b222634/peewee.py#L162-L185

[simonw]

I've changed my mind about this.

"Select every record on the 3rd day of the month" doesn't strike me as an actually useful feature.

"Select every record in 2018 / in May 2018 / on 1st May 2018", if you are using the SQLite-preferred datestring format, are already supported using LIKE queries (or the startswith filter):

• https://fivethirtyeight.datasettes.com/fivethirtyeight/inconvenient-sequel%2Fratings?timestamp__startswith=2017
• https://fivethirtyeight.datasettes.com/fivethirtyeight/inconvenient-sequel%2Fratings?timestamp__startswith=2017-08
• https://fivethirtyeight.datasettes.com/fivethirtyeight/inconvenient-sequel%2Fratings?timestamp__startswith=2017-08-29