Re: [sqlite] Using CTE with date comparison
On Mon, Aug 06, 2018 at 05:02:00PM +, David Raymond wrote: > The whole fencepost thing is probably doing weird things. Here's my take on > it. > This is "time to get from A to B". If you want "Total timespan from A to B > inclusive" then just add 1 day. > > Trying to do the +1 day or -1 day in the middle for the fencepost thing is > probably what's causing the error you mentioned. With one of the days being > the 1st, the -1 day might leave it on the 31st or the 30th or the 28th or the > 29th, depending on the start month. Best to just wait to the very end and say > "oh yeah, plus the start day" > > > with recursive > dates (startDate, endDate) as (values (:startDate, :endDate)), > yearsTable (startDate, years, months, days, resultDate, endDate) > as ( > select min(startDate, endDate), > 0, 0, 0, > min(startDate, endDate), max(startDate, endDate) > from dates > union all > select startDate, years + 1, months, days, > date(startDate, > '+' || cast(years + 1 as text) || ' years'), > endDate > from yearsTable where resultDate < endDate > ), > monthsTable (startDate, years, months, days, resultDate, endDate) > as ( > select * from ( > select * from yearsTable where resultDate <= endDate > order by years desc, months desc, days desc limit 1) > union all > select startDate, years, months + 1, days, > date(startDate, > '+' || cast(years as text) || ' years', > '+' || cast(months + 1 as text) || ' months'), > endDate > from monthsTable where resultDate < endDate > ), > daysTable (startDate, years, months, days, resultDate, endDate) > as ( > select * from( > select * from monthsTable where resultDate <= endDate > order by years desc, months desc, days desc limit 1) > union all > select startDate, years, months, days + 1, > date(startDate, > '+' || cast(years as text) || ' years', > '+' || cast(months as text) || ' months', > '+' || cast(days + 1 as text) || ' days'), > endDate > from daysTable where resultDate < endDate > ) > select startDate, years, months, days, resultDate > from daysTable where resultDate = endDate; > > > startDate years months daysresultDate > -- -- -- -- -- > 2004-02-02 0 0 0 2004-02-02 > 2004-02-02 14 6 1 2018-08-03 > 1983-07-11 1 1 20 1984-08-31 > 1984-11-01 1 2 14 1986-01-15 > 1986-01-16 13 6 0 1999-07-16 > 1970-01-01 48 7 5 2018-08-06 > > > Leap year > > startDate years months daysresultDate > -- -- -- -- -- > 2016-02-28 1 0 1 2017-03-01 > 2015-02-28 1 0 2 2016-03-01 > > > Around the end of a month > > startDate years months daysresultDate > -- -- -- -- -- > 2018-01-15 0 0 30 2018-02-14 > 2018-02-15 0 0 27 2018-03-14 > 2016-02-15 0 0 28 2016-03-14 > 2018-04-15 0 0 29 2018-05-14 With this WITH statement I get outputs which are such as I expected to be. Thank you very much!! -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
The whole fencepost thing is probably doing weird things. Here's my take on it. This is "time to get from A to B". If you want "Total timespan from A to B inclusive" then just add 1 day. Trying to do the +1 day or -1 day in the middle for the fencepost thing is probably what's causing the error you mentioned. With one of the days being the 1st, the -1 day might leave it on the 31st or the 30th or the 28th or the 29th, depending on the start month. Best to just wait to the very end and say "oh yeah, plus the start day" with recursive dates (startDate, endDate) as (values (:startDate, :endDate)), yearsTable (startDate, years, months, days, resultDate, endDate) as ( select min(startDate, endDate), 0, 0, 0, min(startDate, endDate), max(startDate, endDate) from dates union all select startDate, years + 1, months, days, date(startDate, '+' || cast(years + 1 as text) || ' years'), endDate from yearsTable where resultDate < endDate ), monthsTable (startDate, years, months, days, resultDate, endDate) as ( select * from ( select * from yearsTable where resultDate <= endDate order by years desc, months desc, days desc limit 1) union all select startDate, years, months + 1, days, date(startDate, '+' || cast(years as text) || ' years', '+' || cast(months + 1 as text) || ' months'), endDate from monthsTable where resultDate < endDate ), daysTable (startDate, years, months, days, resultDate, endDate) as ( select * from( select * from monthsTable where resultDate <= endDate order by years desc, months desc, days desc limit 1) union all select startDate, years, months, days + 1, date(startDate, '+' || cast(years as text) || ' years', '+' || cast(months as text) || ' months', '+' || cast(days + 1 as text) || ' days'), endDate from daysTable where resultDate < endDate ) select startDate, years, months, days, resultDate from daysTable where resultDate = endDate; startDate years months daysresultDate -- -- -- -- -- 2004-02-02 0 0 0 2004-02-02 2004-02-02 14 6 1 2018-08-03 1983-07-11 1 1 20 1984-08-31 1984-11-01 1 2 14 1986-01-15 1986-01-16 13 6 0 1999-07-16 1970-01-01 48 7 5 2018-08-06 Leap year startDate years months daysresultDate -- -- -- -- -- 2016-02-28 1 0 1 2017-03-01 2015-02-28 1 0 2 2016-03-01 Around the end of a month startDate years months daysresultDate -- -- -- -- -- 2018-01-15 0 0 30 2018-02-14 2018-02-15 0 0 27 2018-03-14 2016-02-15 0 0 28 2016-03-14 2018-04-15 0 0 29 2018-05-14 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál Sent: Monday, August 06, 2018 10:55 AM To: SQLite mailing list Subject: Re: [sqlite] Using CTE with date comparison On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote: > > >Some where in the WITH clause above I want to put '+1 day' in the > >command out there. > > That is because the query does not count the StartDate but does count the > EndDate, so if your EndDate is the next day from the StartDate you get 1 day, > not two. You need to move the fencepost created by the StartDate to > counteract this (so that, effectively, the starting point becomes the day > before the first day, or "day 0") ... This makes the dateY / dateM reflect > the date on which, at the completion of that day, the year or month > respectively was completed: > > WITH RECURSIVE > dates (StartDate, EndDate) as >( > select '2004-02-02', '2004-02-02' >), > yearC (dateY) AS >( > SELECT date(StartDate, '+1 year', '-1 day') > FROM dates > WHERE date(StartDate, '+1 year', '-1 day') <= EndDate > UNION ALL > SELECT date(dateY, '+1 year') > FROM yearC, dates > WHERE date(dateY, '+1 year') <= EndDate >), > years (dateY, years) as >( > SELECT coalesce((SELECT max(dateY) >FROM yearC), date(StartDate, '-1 day')), >c
Re: [sqlite] Using CTE with date comparison
On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote: > > >Some where in the WITH clause above I want to put '+1 day' in the > >command out there. > > That is because the query does not count the StartDate but does count the > EndDate, so if your EndDate is the next day from the StartDate you get 1 day, > not two. You need to move the fencepost created by the StartDate to > counteract this (so that, effectively, the starting point becomes the day > before the first day, or "day 0") ... This makes the dateY / dateM reflect > the date on which, at the completion of that day, the year or month > respectively was completed: > > WITH RECURSIVE > dates (StartDate, EndDate) as >( > select '2004-02-02', '2004-02-02' >), > yearC (dateY) AS >( > SELECT date(StartDate, '+1 year', '-1 day') > FROM dates > WHERE date(StartDate, '+1 year', '-1 day') <= EndDate > UNION ALL > SELECT date(dateY, '+1 year') > FROM yearC, dates > WHERE date(dateY, '+1 year') <= EndDate >), > years (dateY, years) as >( > SELECT coalesce((SELECT max(dateY) >FROM yearC), date(StartDate, '-1 day')), >coalesce((SELECT count(*) >FROM yearC), 0) > FROM dates >), > monthC (dateM) as >( > SELECT date(dateY, '+1 month') > FROM years, dates > WHERE date(dateY, '+1 month') <= EndDate > UNION ALL > SELECT date(dateM, '+1 month') > FROM monthC, dates > WHERE date(dateM, '+1 month') <= EndDate >), > months (dateM, months) as >( > SELECT coalesce((SELECT max(dateM) >FROM monthC), dateY), >coalesce((SELECT count(*) >FROM monthC), 0) > FROM years >), > dayC (dateD) as >( > SELECT date(dateM, '+1 day') > FROM months, dates > WHERE date(dateM, '+1 day') <= EndDate > UNION ALL > SELECT date(dateD, '+1 day') > FROM dayC, dates > WHERE date(dateD, '+1 day') <= EndDate >), > days (dateD, days) as >( > SELECT coalesce((SELECT max(dateD) >FROM dayC), DateM), >coalesce((SELECT count(*) >FROM dayC), 0) > FROM months >) > SELECT StartDate, >DateY, >DateM, >DateD, >EndDate, >years, >months, >days > FROM dates, years, months, days; > > StartDate dateY dateM dateD EndDate years > months days > -- -- -- -- -- -- > -- -- > 2004-02-02 2018-02-01 2018-08-01 2018-08-03 2018-08-03 14 6 > 2 The output of code above is: WITH RECURSIVE dates (StartDate, EndDate) as ( select '2004-02-02', '2004-02-02' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT max(dateY) FROM yearC), date(StartDate, '-1 day')), coalesce((SELECT count(*) FROM yearC), 0) FROM dates ), monthC (dateM) as ( SELECT date(dateY, '+1 month') FROM years, dates WHERE date(dateY, '+1 month') <= EndDate UNION ALL SELECT date(dateM, '+1 month') FROM monthC, dates WHERE date(dateM, '+1 month') <= EndDate ), months (dateM, months) as ( SELECT coalesce((SELECT max(dateM) FROM monthC), dateY), coalesce((SELECT count(*) FROM monthC), 0) FROM years ), dayC (dateD) as ( SELECT date(dateM, '+1 day') FROM months, dates WHERE date(dateM, '+1 day') <= EndDate UNION ALL SELECT date(dateD, '+1 day') FROM dayC, dates WHERE date(dateD, '+1 day') <= EndDate ), days (dateD, days) as ( SELECT coalesce((SELECT max(dateD) FROM dayC), DateM), coalesce((SELECT count(*) FROM dayC), 0) FROM months ) SELECT StartDate, DateY, DateM, DateD, EndDate, years, months, days FROM dates, years, months, days; StartDate dateY dateM dateD EndDate years months days -- -- -- -- -- -- -- -- 2004-02-02 2004-02-01 2004-02-01 2004-02-02 2004-02-02 0 0 1 which is wrong because the StarDate and EndDate are the same: '2004-02-02'. The output should be zero '0' in this case. I tried this SQL command for three cases. The output of two cases are the expected, but one is not. See bellow. 1983-07-11' - '1984-08-31' ^^ sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1983-07-11', '1984-08-31' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT m
Re: [sqlite] Using CTE with date comparison
>Some where in the WITH clause above I want to put '+1 day' in the >command out there. That is because the query does not count the StartDate but does count the EndDate, so if your EndDate is the next day from the StartDate you get 1 day, not two. You need to move the fencepost created by the StartDate to counteract this (so that, effectively, the starting point becomes the day before the first day, or "day 0") ... This makes the dateY / dateM reflect the date on which, at the completion of that day, the year or month respectively was completed: WITH RECURSIVE dates (StartDate, EndDate) as ( select '2004-02-02', '2004-02-02' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT max(dateY) FROM yearC), date(StartDate, '-1 day')), coalesce((SELECT count(*) FROM yearC), 0) FROM dates ), monthC (dateM) as ( SELECT date(dateY, '+1 month') FROM years, dates WHERE date(dateY, '+1 month') <= EndDate UNION ALL SELECT date(dateM, '+1 month') FROM monthC, dates WHERE date(dateM, '+1 month') <= EndDate ), months (dateM, months) as ( SELECT coalesce((SELECT max(dateM) FROM monthC), dateY), coalesce((SELECT count(*) FROM monthC), 0) FROM years ), dayC (dateD) as ( SELECT date(dateM, '+1 day') FROM months, dates WHERE date(dateM, '+1 day') <= EndDate UNION ALL SELECT date(dateD, '+1 day') FROM dayC, dates WHERE date(dateD, '+1 day') <= EndDate ), days (dateD, days) as ( SELECT coalesce((SELECT max(dateD) FROM dayC), DateM), coalesce((SELECT count(*) FROM dayC), 0) FROM months ) SELECT StartDate, DateY, DateM, DateD, EndDate, years, months, days FROM dates, years, months, days; StartDate dateY dateM dateD EndDate years months days -- -- -- -- -- -- -- -- 2004-02-02 2018-02-01 2018-08-01 2018-08-03 2018-08-03 14 6 2 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of pali >Sent: Sunday, 5 August, 2018 07:35 >To: SQLite mailing list >Subject: Re: [sqlite] Using CTE with date comparison > >On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote: >> >> :StartDate and :EndDate are NAMED PARAMETERS for when your >application executes the statement (that is, they are substituted >with the values you want for the StartDate and EndDate respectively. > >I understand now. Thank you! > >> I take it you want to compute YEARS MONTHS and DAYS between two >dates: > >Exactly that is what I want. > >> WITH RECURSIVE >> dates (StartDate, EndDate) as >>( >> select '2004-02-02', '2018-08-03' >>), >> yearC (dateY) AS >>( >> SELECT date(StartDate, '+1 year') >> FROM dates >> WHERE date(StartDate, '+1 year') <= EndDate >> UNION ALL >> SELECT date(dateY, '+1 year') >> FROM yearC, dates >> WHERE date(dateY, '+1 year') <= EndDate >>), >> years (dateY, years) as >>( >> SELECT coalesce((SELECT max(dateY) >>FROM yearC), StartDate), >>coalesce((SELECT count(*) >>FROM yearC), 0) >> FROM dates >>), >> monthC (dateM) as >>( >> SELECT date(dateY, '+1 month') >> FROM years, dates >> WHERE date(dateY, '+1 month') <= EndDate >> UNION ALL >> SELECT date(dateM, '+1 month') >> FROM monthC, dates >> WHERE date(dateM, '+1 month') <= EndDate >>), >> months (dateM, months) as >>( >> SELECT coalesce((SELECT max(dateM) >>FROM monthC), dateY), >>coalesce((SELECT count(*) >>
Re: [sqlite] Using CTE with date comparison
On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote: > > :StartDate and :EndDate are NAMED PARAMETERS for when your application > executes the statement (that is, they are substituted with the values you > want for the StartDate and EndDate respectively. I understand now. Thank you! > I take it you want to compute YEARS MONTHS and DAYS between two dates: Exactly that is what I want. > WITH RECURSIVE > dates (StartDate, EndDate) as >( > select '2004-02-02', '2018-08-03' >), > yearC (dateY) AS >( > SELECT date(StartDate, '+1 year') > FROM dates > WHERE date(StartDate, '+1 year') <= EndDate > UNION ALL > SELECT date(dateY, '+1 year') > FROM yearC, dates > WHERE date(dateY, '+1 year') <= EndDate >), > years (dateY, years) as >( > SELECT coalesce((SELECT max(dateY) >FROM yearC), StartDate), >coalesce((SELECT count(*) >FROM yearC), 0) > FROM dates >), > monthC (dateM) as >( > SELECT date(dateY, '+1 month') > FROM years, dates > WHERE date(dateY, '+1 month') <= EndDate > UNION ALL > SELECT date(dateM, '+1 month') > FROM monthC, dates > WHERE date(dateM, '+1 month') <= EndDate >), > months (dateM, months) as >( > SELECT coalesce((SELECT max(dateM) >FROM monthC), dateY), >coalesce((SELECT count(*) >FROM monthC), 0) > FROM years >), > dayC (dateD) as >( > SELECT date(dateM, '+1 day') > FROM months, dates > WHERE date(dateM, '+1 day') <= EndDate > UNION ALL > SELECT date(dateD, '+1 day') > FROM dayC, dates > WHERE date(dateD, '+1 day') <= EndDate >), > days (dateD, days) as >( > SELECT coalesce((SELECT max(dateD) >FROM dayC), DateM), >coalesce((SELECT count(*) >FROM dayC), 0) > FROM months >) > SELECT StartDate, >DateY, >DateM, >DateD, >EndDate, >years, >months, >days > FROM dates, years, months, days; > > StartDate dateY dateM dateD EndDate years > months days > -- -- -- -- -- -- > -- -- > 2004-02-02 2018-02-02 2018-08-02 2018-08-03 2018-08-03 14 6 > 1 Thank you very much! That's what I wanted, albeit with a small addition: I should get one day more. This is due the fact that when calculated a month, days which belongs to a month are as follows, e.g. for a month which is 31 days long: 1., 2., 3., ..., 30., 31. Some where in the WITH clause above I want to put '+1 day' in the command out there. I am going to study this command which I want to use in a Trigger. Thank you very much again for the solution! -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
:StartDate and :EndDate are NAMED PARAMETERS for when your application executes
the statement (that is, they are substituted with the values you want for the
StartDate and EndDate respectively.
I take it you want to compute YEARS MONTHS and DAYS between two dates:
WITH RECURSIVE
dates (StartDate, EndDate) as
(
select '2004-02-02', '2018-08-03'
),
yearC (dateY) AS
(
SELECT date(StartDate, '+1 year')
FROM dates
WHERE date(StartDate, '+1 year') <= EndDate
UNION ALL
SELECT date(dateY, '+1 year')
FROM yearC, dates
WHERE date(dateY, '+1 year') <= EndDate
),
years (dateY, years) as
(
SELECT coalesce((SELECT max(dateY)
FROM yearC), StartDate),
coalesce((SELECT count(*)
FROM yearC), 0)
FROM dates
),
monthC (dateM) as
(
SELECT date(dateY, '+1 month')
FROM years, dates
WHERE date(dateY, '+1 month') <= EndDate
UNION ALL
SELECT date(dateM, '+1 month')
FROM monthC, dates
WHERE date(dateM, '+1 month') <= EndDate
),
months (dateM, months) as
(
SELECT coalesce((SELECT max(dateM)
FROM monthC), dateY),
coalesce((SELECT count(*)
FROM monthC), 0)
FROM years
),
dayC (dateD) as
(
SELECT date(dateM, '+1 day')
FROM months, dates
WHERE date(dateM, '+1 day') <= EndDate
UNION ALL
SELECT date(dateD, '+1 day')
FROM dayC, dates
WHERE date(dateD, '+1 day') <= EndDate
),
days (dateD, days) as
(
SELECT coalesce((SELECT max(dateD)
FROM dayC), DateM),
coalesce((SELECT count(*)
FROM dayC), 0)
FROM months
)
SELECT StartDate,
DateY,
DateM,
DateD,
EndDate,
years,
months,
days
FROM dates, years, months, days;
StartDate dateY dateM dateD EndDate years months
days
-- -- -- -- -- --
-- --
2004-02-02 2018-02-02 2018-08-02 2018-08-03 2018-08-03 14 6
1
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Sunday, 5 August, 2018 02:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>2018-08-05 0:18 GMT+02:00 Keith Medcalf :
>>
>> WITH RECURSIVE
>> dates(dateD) AS (VALUES(:StartDate)
>> UNION ALL
>> SELECT date(dateD, '+1 year')
>> FROM dates
>>WHERE date(dateD, '+1 year') <= :EndDate
>> )
>> SELECT max(dateD), count(*) FROM dates;
>
>How do I interpret the ':StartDate' and ':EndDate'?
>
>Should I replace for example the ':StartDate' with '1983-07-11' like
>this:
>
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION
>ALL
>SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year')
><= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates;
>1984-07-11|2
>
>But this is ot what I want, because between 1983-07-11 and 1984-08-31
>there is exactly one whole year out there, namely: from 1983-07-11 to
>1984-07-11. So the result value '2' above is wrong in the sense that
>there is 1 year and not 2 years out there.
>
>The following SQL commands gives to me the right output, what I
>desired:
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*)
>FROM dates;
>1984-07-11|1
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*)
>FROM dates;
>1985-11-01|1
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1
>year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*)
>FROM dates;
>1999-01-16|13
>
>but not in the following case:
>sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(d
Re: [sqlite] Using CTE with date comparison
2018-08-05 0:18 GMT+02:00 Keith Medcalf :
>
> WITH RECURSIVE
> dates(dateD) AS (VALUES(:StartDate)
> UNION ALL
> SELECT date(dateD, '+1 year')
> FROM dates
>WHERE date(dateD, '+1 year') <= :EndDate
> )
> SELECT max(dateD), count(*) FROM dates;
How do I interpret the ':StartDate' and ':EndDate'?
Should I replace for example the ':StartDate' with '1983-07-11' like this:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION ALL
SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year')
<= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates;
1984-07-11|2
But this is ot what I want, because between 1983-07-11 and 1984-08-31
there is exactly one whole year out there, namely: from 1983-07-11 to
1984-07-11. So the result value '2' above is wrong in the sense that
there is 1 year and not 2 years out there.
The following SQL commands gives to me the right output, what I desired:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*)
FROM dates;
1984-07-11|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*)
FROM dates;
1985-11-01|1
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*)
FROM dates;
1999-01-16|13
but not in the following case:
sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1
year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*)
FROM dates;
2001-02-01|1
because here in the last example there should be '0' and not '1' in
the output of that command.
I am still trying to find the proper way to do this, what I desired,
if it is possible at all.
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
WITH RECURSIVE
dates(dateD) AS (VALUES(:StartDate)
UNION ALL
SELECT date(dateD, '+1 year')
FROM dates
WHERE date(dateD, '+1 year') <= :EndDate
)
SELECT max(dateD), count(*) FROM dates;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Saturday, 4 August, 2018 14:36
>To: SQLite mailing list
>Subject: Re: [sqlite] Using CTE with date comparison
>
>2018-08-04 14:07 GMT+02:00 Csányi Pál :
>> 2018-08-03 13:09 GMT+02:00 R Smith :
>>
>>> 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?
>>
>> I want a recursive query that gives years to the start date until
>it
>> reaches the end date or if it exceeds the end date, the last added
>> date is valid. Meanwhile, it should report how many times it has
>added
>> a year. Its output is the date of the last added year and the
>number
>> of additions. Is this possible?
>
>I think I found the solution.
>Here is two SQL statement with different dates, and it seems to me
>that that it do it right, no?
>
>sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1983-07-11','+1
>year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD) <= date('1984-08-31') ) SELECT date(dateD), count(*) FROM
>dates WHERE dateD <= '1984-08-31';
>1984-07-11|1
>
>sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1984-11-01','+1
>year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
>date(dateD) <= date('1986-01-15') ) SELECT date(dateD), count(*) FROM
>dates WHERE dateD <= '1986-01-15';
>1985-11-01|1
>
>--
>Best, Pali
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
2018-08-04 14:07 GMT+02:00 Csányi Pál :
> 2018-08-03 13:09 GMT+02:00 R Smith :
>
>> 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?
>
> I want a recursive query that gives years to the start date until it
> reaches the end date or if it exceeds the end date, the last added
> date is valid. Meanwhile, it should report how many times it has added
> a year. Its output is the date of the last added year and the number
> of additions. Is this possible?
I think I found the solution.
Here is two SQL statement with different dates, and it seems to me
that that it do it right, no?
sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1983-07-11','+1
year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD) <= date('1984-08-31') ) SELECT date(dateD), count(*) FROM
dates WHERE dateD <= '1984-08-31';
1984-07-11|1
sqlite> WITH RECURSIVE dates(dateD) AS ( SELECT date('1984-11-01','+1
year') UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE
date(dateD) <= date('1986-01-15') ) SELECT date(dateD), count(*) FROM
dates WHERE dateD <= '1986-01-15';
1985-11-01|1
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
2018-08-03 13:09 GMT+02:00 R Smith : > 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? I want a recursive query that gives years to the start date until it reaches the end date or if it exceeds the end date, the last added date is valid. Meanwhile, it should report how many times it has added a year. Its output is the date of the last added year and the number of additions. Is this possible? -- Best, Pali ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
On Fri, 3 Aug 2018 15:11:06 +0200, Csányi Pál wrote: >The database is so far with only one table: >CREATE TABLE MyLengthOfService ( >id INT PRIMARY KEY > UNIQUE, >WorkPlaceName TEXT, You shouldn't use UNIQUE for the PRIMARY KEY. Any PRIMARY KEY is implicitly unique by itself. Adding the UNIQUE keyword might create an extra, redundant, index, which will eat file space and processing time without having any added value. And it may confuse the query optimizer. The idea is that any set (table) may have more than one key to uniquely identify a tuple (row). Each key is called a candidate key. Only one of those keys can be choosen to be the PRIMARY KEY. All other candidate keys can get honored by adding the UNIQUE clause, to recognize them as alternative keys. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
2018-08-03 13:09 GMT+02:00 R Smith :
>
>
> On 2018/08/03 12:35 PM, Csányi Pál wrote:
>>
>> 2018-08-02 23:12 GMT+02:00 R Smith :
>>>
>>> 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?
I will try to explain what is the question and will provide two or
three examples.
I am working on a SQLite database which I want to use for calculation
for the Length Of Service for me as a worker.
The database is so far with only one table:
CREATE TABLE MyLengthOfService (
id INT PRIMARY KEY
UNIQUE,
WorkPlaceName TEXT,
StartDate DATE,
EndDate DATE,
WithWorkingTime INT,
Comment TEXT,
Years INT,
RemainingMonths INT,
RemainingDays INT
);
I want to create a Trigger which would calculate Years,
RemainingMonths, and RemainingDays for a WorkPlace.
So far the Trigger is this:
UPDATE MyLengthOfService SET Years = ( WITH RECURSIVE dates(dateD) AS
( SELECT StartDate FROM MyLengthOfService WHERE End
Re: [sqlite] Using CTE with date comparison
On 2018/08/03 12:35 PM, Csányi Pál wrote:
2018-08-02 23:12 GMT+02:00 R Smith :
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
Re: [sqlite] Using CTE with date comparison
2018-08-02 23:12 GMT+02:00 R Smith :
> 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?
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
2018-08-02 23:12 GMT+02:00 R Smith :
> 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
Ryan, thank you very much for the explanation!
Then, how can I implement steps from 1 to 7 in SQLite language?
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CTE with date comparison
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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using CTE with date comparison
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?
--
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
