1

I need a solution extension that I received and it is visible at the link How to get correct year, month and day in firebird function datediff. I thought I had the right solution, but it turned out that the results I get differ from those counted by hand for a few days. The analysis showed that the reason of this differ was a leap years. Does anyone have an idea how to update this query to include leap years in counting dates?

I have got sql query forom Livius like this:

SELECT
KP3.id_contact 
, (KP3.D2-KP3.D1) / (12*31) AS Y
, ((KP3.D2-KP3.D1) - ((KP3.D2-KP3.D1) / (12*31)) * 12 * 31) / 31 AS M
, CAST(MOD((KP3.D2-KP3.D1) - (((KP3.D2-KP3.D1) / (12*31)) * 12 * 31), 31) AS INTEGER) AS D
FROM
(SELECT
KP2.id_contact, SUM(KP2.D1) AS D1, SUM(KP2.D2) AS D2
FROM
    (
    SELECT
    KP.id_contact, DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO) / 12 AS Y, CAST(MOD(DATEDIFF(MONTH, KP.DATE_FROM, KP.DATE_TO), 12) AS INTEGER) AS M 
    , EXTRACT(YEAR FROM KP.DATE_FROM)*12*31+EXTRACT(MONTH FROM KP.DATE_FROM)*31+EXTRACT(DAY FROM KP.DATE_FROM) D1
    , EXTRACT(YEAR FROM KP.DATE_TO)*12*31+EXTRACT(MONTH FROM KP.DATE_TO)*31+EXTRACT(DAY FROM KP.DATE_TO) D2 
    FROM
    KP  
    ) AS KP2
GROUP BY KP2.id_contact
) AS KP3

For example I have got interval from 2011-11-02 to 2014-08-31. When I use this query I get result 2Y 9M 29D. But when I calculate this on hand I get result 2Y 9M 30D. It is one day differ because year 2012 have 366 days not 365 because 2012 is leap year.

jeffers
  • 145
  • 3
  • 13
  • 2
    You should really show examples of the code you are using, what Firebird returns and what you actually want. – Gordon Linoff Jan 24 '20 at 23:42
  • 1
    Ok, one moment and I update my question. – jeffers Jan 24 '20 at 23:48
  • `(12*31)` - is doomed to failure, because not all months have 31 days. You're going to have weird corner cases. Do you want the entire period directed (all components have the same sign), or not? Do you have a calendar table with epoch day/month numbers? – Clockwork-Muse Jan 25 '20 at 00:16
  • Does [my answer here](https://dba.stackexchange.com/a/255618/8283) help. This solution is for Firebird 3 – Mark Rotteveel Jan 25 '20 at 09:18
  • 2
    Does your expectation actually hold: 2011-11-02 + 2 years is 2013-11-02 + 9 months is 2014-08-02 + 29 days is 2014-08-31. How do you come to the conclusion it should be 2Y 9M 30D? (which according to my calculations would be 2014-09-01). Leap years shouldn't matter except when starting in February of a leap year. – Mark Rotteveel Jan 25 '20 at 09:48
  • 1
    I see no error there. Because months and years are different from each other you can not correctly measure time DISTANCE in months. it would be like measuring geographical distance in cities. How many New Yorks lie between London and Paris? How many Warsaws high is Elbrus mountain? You can not have any mathematically correct answer. Thus you can only answer with NON-PRECISE estimations. Suitable for give-or-take kind of street talk. So, your `DateDiff` query just gave you perfectly valid answer "2Y 10M give or take few days" - the answer IS valid for the context.. – Arioch 'The Jan 27 '20 at 12:49
  • 1
    Also, imagine you get the span of about 6Y, how many leap years should you accoutn for? In the "6Y" from 1999 to 2004 there were TWO leap years, but in the same "6Y" from 1998 to 2003 there only was ONE leap year. And then we have milleniums, where 2000 was leap year but 1900 was not. And same "sliding window" problem gives you volatile undefined number of leap years in timespans like "110Y". If you want to go toward layman and count timespans in "years and months" - you have to agree this makes thing easy, simple and imprecise by definition. And mismatch of one day over 3 years is norm, is OK – Arioch 'The Jan 27 '20 at 12:55

0 Answers0