On Mon, Aug 06, 2018 at 05:02:00PM +0000, 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 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
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
