All geek questions in one place

How to create all dates between two dates

How can I get all dates between '2015-10-02' to '2015-11-02' in SQLite? (String type) The result will look like this:

'2015-10-03' '2015-10-04' '2015-10-05' ... '2015-11-01'

This is not a question about SELECT * FROM myTable where myDate <= '2015-01-01' AND myDate >= '2015-01-31' . It is not about selecting all existing entries that have a field between two days. I just want to get all possible date values ​​between two dates. I want to use them to query the number of entries by day.

 Date Count '2015-01-01' 19 '2015-01-02' 10 '2015-01-03' 0 ...
+5
source share
2 answers

This is not possible without the recursive regular table expression that 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;
+9
source

If the date column is already a date type with the format YYYY-MM-DD , you can use the following query:

 SELECT date_field AS Date, COUNT(date_field) FROM table WHERE (date_field BETWEEN '2015-10-02' AND '2015-11-02') GROUP BY date_field

If the date column is actually a string, you can use the strftime() function:

 SELECT date_field AS Date, COUNT(date_field) FROM table WHERE strftime('%Y-%m-%d', date_field) BETWEEN '2015-10-02' AND '2015-11-02' GROUP BY date_field
0
source

Source: https://habr.com/ru/post/1233104/

More articles:


All Articles