SQLite:获取日期之间的所有日期 [英] SQLite: Get all dates between dates
问题描述
我需要有关SQL(特别是SQLite)相关问题的帮助.我有一个桌子假期"
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
);
我存储日期(YYYY-MM-DD)的位置,当某人离开度假并再次回来时.现在,我想获得一个独特的清单,其中列出某人正在度假的所有日期.假设我的表格如下:
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 |
+------------+-------------+------------+
查询的结果应类似于:
+------------------------------+
| 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 |
+------------------------------+
我考虑过使用日期-表'all_dates'
I thought about using a date - table 'all_dates'
CREATE TABLES all_dates(
date_entry TEXT
);
涵盖20年的时间跨度(2010年1月1日至2030年1月1日)和以下查询:
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 |
+------------------------------+
可以使用SQLite完成吗?还是更好,如果我只返回"to_date"和"from_date"列,并在我的Python应用程序中填补这些日期之间的空白?
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?
感谢您的帮助!
推荐答案
您可以尝试:
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
这篇关于SQLite:获取日期之间的所有日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!