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 days resultDate
---------- ---------- ---------- ---------- ----------
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 days resultDate
---------- ---------- ---------- ---------- ----------
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 days resultDate
---------- ---------- ---------- ---------- ----------
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:[email protected]] 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')),
> 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 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1983-07-11 1984-07-10 1984-08-10 1984-08-31 1984-08-31 1 1
21
This is the expected output.
'1984-11-01' - '1986-01-15'
^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1984-11-01',
'1986-01-15' ), 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1984-11-01 1985-10-31 1986-01-01 1986-01-15 1986-01-15 1 2
14
This is not the expected output.
The expected output would be 1 years, 2 months, but 15 days instead.
Why gives it 14 days instead?
'1986-01-16' - '1999-07-16'
^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1986-01-16',
'1999-07-16' ), 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
---------- ---------- ---------- ---------- ---------- ----------
---------- ----------
1986-01-16 1999-01-15 1999-07-15 1999-07-16 1999-07-16 13 6
1
This is the expected output.
So I am asking: why is the output of above examples in two cases as I
am expecting, but in other two cases is not?
The expected outputs is good only if does match that value which I am
calculate manually, without SQL query, by using logic that I described
earlier in this thread.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
