On 2018/08/03 12:35 PM, Csányi Pál wrote:
2018-08-02 23:12 GMT+02:00 R Smith <ryansmit...@gmail.com>:
On 2018/08/02 10:29 PM, Csányi Pál wrote:
Hi,
I just want to know why the following SQLite query does not work as I
expected?
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD) FROM dates;
2017-10-03|3
which is not what I am expecting.
I am expecting the followings:
1. the query add to the date('2015-10-03') 1 year which is '2016-10-03'
2. then it compares the two dates: 2016-10-03 with 2016-10-03
3. because 2016-10-03 = 2016-10-03 it count 1
4. then add to the result date 2016-10-03 once again 1 year which is
2017-10-03
5. then it compares the two dates: 2017-10-03 with 2016-10-03
6. because 2017-10-03 > 2016-10-03 it does not count 2
7. it should gives the following result:
2016-10-03|1
What am I doing wrong here?
When the recursive Query starts up, the first value that it outputs is given
by the very first part of the query, namely: VALUES('2015-10-03')
So on the first iteration, it will produce one row like this:
'2015-10-03'
regardless of the rest of the Query. This row is pushed into the recursion
buffer.
After that it then reads a row from the recursion buffer and checks (within
the WHERE clause) whether the value in it (namely: '2015-10-03') is <=
'2016-11-01', and finds that it definitely IS less, so continues to produce
the another line of output.
The output created is that date from the buffer (2015-10-03) which is put
through the given calculation: date(dateD, '+1 year') to give:
'2016-10-03'
It then continues to push that next row into the recursion buffer and next
reads again from it and again checks if it (2016-10-03) is <= than
2016-11-01, which again it is... so it continues to produce the next output
row, which after calculation becomes:
'2017-10-03'
It then continues to push that again into the buffer and again read it and
again checks if it (2017-10-03) is less than 2016-11-01, which THIS TIME, it
isn't... so it stops right there.
So in the end, it has produced 3 output rows namely:
'2015-10-03'
'2016-10-03'
'2017-10-03'
Which is exactly what you've asked for.
Note: The first part of the query will ALWAYS reach the output buffer, even
if it isn't a recursive query, and the UNION is NOT specified, you will get
at least the '2015-10-03' value.
Note: When comparing in the WHERE clause, you do not compare the newly
calculated value (date(xxx, +1 year)), but indeed you compare the
before-calculated value, i.e. the previous value in the buffer (the same as
how your calculation is done on the PREVIOUS value in the buffer to yield
the new date with.
I hope that helps to make sense.
Cheers,
Ryan
I think then that that the following SQL query gives to me the desired result:
WITH RECURSIVE dates(dateD) AS ( VALUES('2015-10-03') UNION ALL SELECT
date(dateD, '+1 year') FROM dates WHERE date(dateD) <=
date('2016-11-01') ) SELECT max(dateD), count(dateD)-2 FROM dates;
count(dateD)-2 does the math. Right?
That depends on what the desired result is. Do you want to count how
many years elapsed (in full) since 3 October 2015? Count()-2 is the
worst hack for this (unless the question is specifically "what is 2 less
than the number of years between x and y....").
Do you want a recursive query that will add all years between 3 October
and some other date, but NOT the first year and NOT the last year?
Maybe if you explain what is the question you are asking, and perhaps
provide 2 examples, the one you already done is fine, but give another
one where the dates are from 2015-10-03 to 2025-11-01 or such, how must
that date-range be answered by the query?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
