SQLite: Get all dates between dates
between, date, sql, sqlite
Solution
You can try that:
SELECT date_entry
FROM vacation
JOIN all_dates ON date_entry BETWEEN from_date AND to_date
GROUP BY date_entry
ORDER BY date_entry
Problem
I need some help with a SQL (in particular: SQLite) related problem. I have a table 'vacation' ``` CREATE TABLE vacation( name TEXT, to_date TEXT, from_date TEXT ); ``` where I store the date (YYYY-MM-DD), when somebody leaves for vacation and comes back again. Now, I would like to get a distinctive list of all dates, where somebody is on vacation. Let's assume my table looks like: ``` +------------+-------------+------------+ | name | to_date | from_date | +------------+-------------+------------+ | Peter | 2013-07-01 | 2013-07-10 | | Paul | 2013-06-30 | 2013-07-05 | | Simon | 2013-05-10 | 2013-05-15 | +------------+-------------+------------+ ``` The result from the query should look like: ``` +------------------------------+ | dates_people_are_on_vacation | +------------------------------+ | 2013-05-10 | | 2013-05-11 | | 2013-05-13 | | 2013-05-14 | | 2013-05-15 | | 2013-06-30 | | 2013-07-01 | | 2013-07-02 | | 2013-07-03 | | 2013-07-04 | | 2013-07-05 | | 2013-07-06 | | 2013-07-07 | | 2013-07-08 | | 2013-07-09 | | 2013-07-10 | +------------------------------+ ``` I thought about using a date - table 'all_dates' ``` CREATE TABLES all_dates( date_entry TEXT ); ``` which covers a 20 year time span (2010-01-01 to 2030-01-01) and the following query: ``` SELECT date_entry FROM all_dates WHERE date_entry BETWEEN (SELECT from_date FROM vacation) AND (SELECT to_date FROM vacation); ``` However, If i apply this query on the above dataset, I only get a fraction of my desired result: ``` +------------------------------+ | dates_people_are_on_vacation | +------------------------------+ | 2013-07-01 | | 2013-07-02 | | 2013-07-03 | | 2013-07-04 | | 2013-07-05 | | 2013-07-06 | | 2013-07-07 | | 2013-07-08 | | 2013-07-09 | | 2013-07-10 | +------------------------------+ ``` Can it be done with SQLite? Or it is better, if I just return the 'to_date' and 'from_date' column and fill the gaps between these dates in my Python application? Any help is appreciated!