如何生成两个日期之间的所有日期 [英] How to generate all dates between two dates
问题描述
如何在SQLite中检索2015-10-02至2015-11-02之间的所有日期? (String type)
结果将如下:
How can I retrieve all dates between '2015-10-02' to '2015-11-02' in SQLite? (String type) Result will be like:
'2015-10-03'
'2015-10-04'
'2015-10-05'
...
'2015-11-01'
这是不关于 SELECT * FROM myTable的问题,其中myDate< ='2015-01-01'AND myDate > ='2015-01-31'
。这是不关于选择所有现有的记录,其中有两天之间的字段。我只想检索两个日期之间的所有可能的日期值。我想使用它们来查询日期记录数。
This is not a question about SELECT * FROM myTable where myDate <= '2015-01-01' AND myDate >= '2015-01-31'
. This is not about selecting all existing records which have a field between two days. I just want to retrieve all possible date values between two dates. I want to use them to query the count of record by days.
Date Count
'2015-01-01' 19
'2015-01-02' 10
'2015-01-03' 0
...
推荐答案
如果没有递归普通表表达式,它是在SQLite 3.8.3中引入的:
This is not possible without a recursive common table expression, which was introduced in SQLite 3.8.3:
WITH RECURSIVE dates(date) AS (
VALUES('2015-10-03')
UNION ALL
SELECT date(date, '+1 day')
FROM dates
WHERE date < '2015-11-01'
)
SELECT date FROM dates;
这篇关于如何生成两个日期之间的所有日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!