Hi List,

i'm searching a pascal datetime function that simulates an Excel function: "DateDif"

Excel for example knows the function DateDif that returns the number of Years, month and days between Date1 and date2.

Date1 := 21.12.2012
Date2 := 01.01.2013

Result would be: 0 years, 0 moths, 11 days

I tried to do it on my own, but without success.

But i think there is a function out there but i don't know its name :-((

Thanks for your tipps

John

Try the DaysBetween function::
http://www.freepascal.org/docs-html/rtl/dateutils/daysbetween.html

But, I am not sure how you will deal with "months between" unless you settle on 31 days per month.

uses
  SysUtils;
var
  Date1,Date2: TDate;
  Y,M,D: Word;
begin
  Date1 := EncodeDate(2012,12,21);
  Date2 := EncodeDate(2013,01,01);
  DecodeDate(Date2 - Date1,Y,M,D);
  // you have them in Y, M and D, respectively
end.

I have written my own function because I wanted to store dates in YYYY.MM.DD.MM.SS format and didn't
like any conversions back and forth between other
formats like TDateTime etc.

Find attached a time-diff routine based on some types (also included). Maybe it is of help for you.
(Comments and function names are partly in german so you may need to change them).

-------------- next part --------------
A non-text attachment was scrubbed...
Name: TimeDiff.pas
Type: text/x-pascal
Size: 5654 bytes
Desc: not available
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131109/89eb3766/attachment-0001.bin>

The dateutils unit contains DaysBetween, MinutesBetween, HoursBetween, etc. Each function exists in 2 variants: one which takes partial times, another which does complete days/hours/minutes.

See
http://www.freepascal.org/docs-html/rtl/dateutils/index-5.html

For the documentation.

Michael.

I can't find any functions that take year/month/date as arguments. The documentation only mentions TDateTime parameters. (i.e. http://www.freepascal.org/docs-html/rtl/dateutils/daysbetween.html )

So what ? Do an EncodeDate() and you're all set.
Excel also uses TDateTime, that's where the format comes from.

Michael.

leledumbo wrote

Date1 := EncodeDate(2012,12,21); Date2 := EncodeDate(2013,01,01);

Your code Result is: 1900 Years, 1 Month, 10 days and thats incorrect.

I thought about it some time and found a solution:

Think of the age of an employe! We have now() and his birthday. How old is he in Years , Months and days?

my code:

uses DateUtils;
function TForm1.CalcDateDif(von, bis: TDateTime): String;
var
   Year, Month, Day: longint;
   YearVon, MonthVon, DayVon : word;
   YearBis, MonthBis, DayBis : word;
begin
   DecodeDate(von, YearVon, MonthVon, DayVon);
   DecodeDate(bis, YearBis, MonthBis, DayBis);
   // whole year:
    year := YearsBetween(von,bis);
   // How many months more than months in "year"
    Month := MonthsBetween(von,bis) - (year * 12);
   // Special case: December of previous year as "von"  and not a whole
Month until "bis": 21.12.2012 until 01.01.2013
   if  (YearBis - YearVon = 1) and (MonthVon = 12) then
    begin
      Day :=  DaysBetween(von,EndOfAYear(YearVon)) + DayOfTheYear(bis);
    end
    else
       Day := DaysBetween(EnCodeDate(YearBis,MonthVon, DayVon), bis) ;
     Result := IntToStr(year) + ' Jahr(e) ';
      Result := Result + IntToStr(Month) + ' Monat(e) ';
     Result := Result + IntToStr(Day) + ' Tag(e) ';
end;
// I have to test that later again, but i think thats ok now?!!
// Going jogging now :-))

But i'm really wondering that Freepascal does not have such a function?!!

Indeed that was wrong, I shouldn't exploit the internal details. Below is the correct one (still no need for that complicated code of yours):

uses
  SysUtils,DateUtils;
var
  Date1,Date2: TDate;
  Y,M,D: Word;
begin
  Date1 := EncodeDate(2012,12,21);
  Date2 := EncodeDate(2013,01,01);
  Y := YearsBetween(Date1,Date2);
  M := MonthsBetween(Date1,Date2);
  D := DaysBetween(Date1,Date2);
  WriteLn(Y,'-',M,'-',D);
end.

yeah, that doesn't work...

Y = 1900
M = 1
D = 10

Y should be 1 in this case...

ideally, real numbers would be used so that fractions of years, months and days can be determined ;)

please test your code: 21.12.2010(!!) 01.01.2013

My code is wrong too in this case :--((

Michael Van Canneyt wrote

So what ? Do an EncodeDate() and you're all set.

That would be a real performance hit (i.e. when sorting by date) if you have stored dates in year, month, day... format. Each comparison then needs multiple convertions each time.

Not if you store everything as a DateTime and use that. The format is such that it sorts naturally.

But hey, who am I to try and convince you ?

Michael.

disregarding the above writeln being incorrect for the values represented, even this has problems... each value is the total value between the dates... YearsBetween and MonthsBetween are short by 1...

2013/01/01
2013/01/01
0 yrs, 0 mos, 0 wks, 0 dys

above is correct (of course)...

2013/01/01
2014/01/01
0 yrs, 11 mos, 52 wks, 365 dys

above should be 1 yrs, 12 mos... weeks and days are correct...

2013/01/01
2015/01/01
1 yrs, 23 mos, 104 wks, 730 dys

above should be 2 yrs, 24 mos... weeks and days are correct...

program DateDiff;
uses
   SysUtils,DateUtils;
var
   Date1,Date2: TDate;
   Yr,Mo,Wk,Dy: Integer;
begin
   Date1 := EncodeDate(2013,01,01);
   Date2 := EncodeDate(2015,01,01);
   writeln(FormatDateTime('YYYY/MM/DD HH:NN:SS.ZZZ',Date1));
   writeln(FormatDateTime('YYYY/MM/DD HH:NN:SS.ZZZ',Date2));
   Yr := YearsBetween(Date1,Date2);
   Mo := MonthsBetween(Date1,Date2);
   Wk := WeeksBetween(Date1,Date2);
   Dy := DaysBetween(Date1,Date2);
   writeln(Yr,' yrs, ',Mo,' mos, ',Wk,' wks, ',Dy,' dys');
end.

Storing dates as floats has a lot of drawbacks.
It's inaccurate, you always need a starting date and get other problems. I shudder when thinking about Excels 1900 and 1904 based dates where suddenly all my dates in the sheet where changed by 4 years because the base has changed. ;-(

Can you prove this statement ?

I use this type since more than 15 years, never experienced a glitch.

Of course, I didn't have to represent stardate XYZ.NMO, but in my opinion, TDateTime is usable for 99,99% of all use cases.

Michael.

digging deeper to work directly with YearsBetween and MonthsBetween shows that they seem to be missing the usual "+0.5" generally used to ensure that rounding is properly handled...

the following two pairs of routines work to give the desired output...

*this pair uses Trunc((X)+.05) instead of the original Trunc(X)*

Function YearsBetween(const ANow, AThen: TDateTime): Integer;
begin
Result:=Trunc(((Abs(MyDateTimeDiff(ANow,AThen))+TDateTimeEpsilon)/ApproxDaysPerYear)+0.5);
end;
Function MonthsBetween(const ANow, AThen: TDateTime): Integer;
begin
Result:=Trunc(((Abs(MyDateTimeDiff(ANow,AThen))+TDateTimeEpsilon)/ApproxDaysPerMonth)+0.5);
end;

note the additional '(' after 'Trunc' and the closing '+0.5)' which handles the rounding as compared to the original routines in DateUtil.inc...

*OR this pair using Round() instead of the original Trunc(X) or the above Trunc((X)+0.5)*

Function YearsBetween(const ANow, AThen: TDateTime): Integer;
begin
Result:=Round((Abs(MyDateTimeDiff(ANow,AThen))+TDateTimeEpsilon)/ApproxDaysPerYear);
end;
Function MonthsBetween(const ANow, AThen: TDateTime): Integer;
begin
Result:=Round((Abs(MyDateTimeDiff(ANow,AThen))+TDateTimeEpsilon)/ApproxDaysPerMonth);
end;

i don't know which would be better to use in this case but one should be applied to the existing routines in DateUtil.inc to fix the error in them ;)

waldo kitty wrote

Result:=Trunc(((Abs(MyDateTimeDiff(ANow,AThen))+TDateTimeEpsilon)/ApproxDaysPerYear)+0.5); end;

I thought that correct results should be calculated, not only "approximate" values. What is "ApproxDaysPerYear"?
A leap year has 266 days and others 365 and this should be considered. Calculations based on floats seem not very reliable.

from DateUtil.inc

[quote]
Const
   { Average over a 4 year span. Valid for next 100 years }
   ApproxDaysPerMonth: Double = 30.4375;
   ApproxDaysPerYear: Double  = 365.25;
[/quote]

of course, the question is now "what is the starting and ending points of 'next 100 years'?"... ideally it should state the range of years that it is valid for, shouldn't it?? ;)

waldo kitty wrote

digging deeper to work directly with YearsBetween and MonthsBetween shows that they seem to be missing the usual "+0.5" generally used to ensure that rounding is properly handled...

You misunderstood these functions.

There is no rounding necessary. The routines are correct.

Only FULL years/months/day must be counted.

See the documentation in e.g.

http://www.freepascal.org/docs-html/rtl/dateutils/yearsbetween.html

If you want partial years counted, you need the *Span functions.

http://www.freepascal.org/docs-html/rtl/dateutils/yearspan.html

Michael.

Michael Van Canneyt wrote

On Sat, 9 Nov 2013, J?rgen Hestermann wrote: > Storing dates as floats has a lot of drawbacks. > It's inaccurate, you always need a starting date and get other problems. Can you prove this statement ?

Well, aren't the current mails about incorrect YearsBetween calculation proof enough? If you need to use an approximate value for days in the year then some is wrong IMO. For the same difference between two time floats it can be a full year or not depending on whether a leap year is included or not.

They are not incorrect.
The reporter didn't understand what they are supposed to do.

If you need to use an approximate value for days in the year then some is wrong IMO.

Not if it is clearly documented.

Michael.

if you have stored dates in year, month, day... format. Each comparison then needs multiple convertions each time.

nonsense.

Store it as packed record word-byte-byte = int and you have faster comparisons than TDateTime!

Found a solution in Jedi: /jvcl/run/JvJCLUtils.pas

procedure DateDiff(Date1, Date2: TDateTime; var Days, Months, Years: Word
   );
var
   DtSwap: TDateTime;
   Day1, Day2, Month1, Month2, Year1, Year2: Word;
begin
   if Date1 > Date2 then
   begin
     DtSwap := Date1;
     Date1 := Date2;
     Date2 := DtSwap;
   end;
   DecodeDate(Date1, Year1, Month1, Day1);
   DecodeDate(Date2, Year2, Month2, Day2);
   Years := Year2 - Year1;
   Months := 0;
   Days := 0;
   if Month2 < Month1 then
   begin
     Inc(Months, 12);
     Dec(Years);
   end;
   Inc(Months, Month2 - Month1);
   if Day2 < Day1 then
   begin
     // von mir auskommentiert Inc(Days, DaysPerMonth(Year1, Month1));
     Inc(Days, DaysInAMonth(Year1, Month1));
     if Months = 0 then
     begin
       Dec(Years);
       Months := 11;
     end
     else
       Dec(Months);
   end;
   Inc(Days, Day2 - Day1);
end;

**************************************

Seems to calculate ok.
Now i'll try to understand what it is doing :-))

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131109/6eb7794a/attachment.html>

ahhh...

See the documentation in e.g. http://www.freepascal.org/docs-html/rtl/dateutils/yearsbetween.html

yes, i've been digging in them all day since this thread came up ;)

If you want partial years counted, you need the *Span functions. http://www.freepascal.org/docs-html/rtl/dateutils/yearspan.html

i'll have to look at those... i've been looking for something that can (without having to reinvent the wheel) show that the difference between

2013 01 01 00:00:00.000 and
2013 01 01 00:00:00:001 is
0 Years, 0 Months, 0 Days, 0 Hours, 0 Minutes, 0 Seconds, 1 Msecond

whereas

2013 01 01 00:00:00.000 and
2013 01 01 00:00:00:100 is
0 Years, 0 Months, 0 Days, 0 Hours, 0 Minutes, 0 Seconds, 100 Msecond

and

2013 01 01 00:00:00.000 and
2013 01 01 00:00:01:001 is
0 Years, 0 Months, 0 Days, 0 Hours, 0 Minutes, 1 Seconds, 1 Msecond

and

2013 01 01 00:00:00.000 and
2014 02 02 01:01:01:001 is
1 Years, 1 Months, 1 Days, 1 Hours, 1 Minutes, 1 Seconds, 1 Msecond

and also being able to include Weeks in there if we are eg: 1, 2 or 3 weeks into a month...

i will take a look at the SPAN functions and see if they will provide what i've been (silently) looking for ;)

i misunderstood the given functions... sorry :?

If you need to use an approximate value for days in the year then some is wrong IMO.

i have been working on this most all day and there's no way around it with simple functions... they must be more intricate than what i've seen or created so far...

For the same difference between two time floats it can be a full year or not depending on whether a leap year is included or not.

and there's the rub... if you want the difference between two dates and times, how do you calculate if there is a leap year involved, which year of the date is involved and how do you feed that to the *Between routines? you can't from what i've seen... however the *Span routines i've been pointed to may very well handle this...

what i've seen is like comarping this

2013/01/01 00:00:00.000
2014/01/01 00:00:00.001

and getting this
MilliSecondsBetween(A1,A2) = 31536000001

but this is an estimated value because

RealMSPerYear = 31557600000

is what you get with 365.25 days per year...

but then looking at those two values, you cannot tell if a leap year is involved in there or not... much less if there are two or more leap years...

back in the day, routines that i used to do this in TP/BP converted the date/time values to julian date values, worked on them for the difference and then converted back to individual year, months, day, etc... but then things break when traversing over period count changes or trying to go between different calendar formats... it was ugly then and still ugly now... i just hope that what i had then is available now via built-in library routines or is easily built from the library routines ;)

actually, in the name of accuracy, it is highly desirable... being able to determine if a date range spans one or more leap years is important... too bad there's not a set routine for when leap seconds are added... things like this really rattle the cages of those of us with OCD where accuracy in values is highly desirable ;)

FWIW: i discovered, later, that my +0.05 didn't work as desired in all situations :( gonna finish eating and reading emails and then maybe go digging again O:)

I'm not sure, IMO also M and D should be decremented by 1.

ideally, real numbers would be used so that fractions of years, months and days can be determined ;)

Of course real numbers are used. Problem is the time *base* (calendar origin), that (here) translates zero into year 1900, and IMO also offsets the month and day by 1. Delphi may yield a different year, AFAIR its calendar is based on 1899-12-30 12:00.

The day *difference* is the integral part of real difference, i.e. trunc(Date2-Date1). But for the elimination of all offsets I'd suggest to determine all base values, and subtract these from the individual values. Something like

   DecodeDate(0.0, Y0,M0,D0);
   DecodeDate(Date2-Date1,Y,M,D);
   dec(Y,Y0); dec(M,M0); dec(D,D0);

But this still wouldn't be correct, because then the leap years (february length) of 1900 ff. are taken into account, not those of the start year. Better should be:

   DecodeDate(Date1, Y0,M0,D0);
   DecodeDate(Date2,Y,M,D);
   dec(Y,Y0); dec(M,M0); dec(D,D0);

with the ugly effect that now M and D can become negative and have to be adjusted again.

So the general question arises: do you want a date difference expressed in uniform years (of 360 days) and months (of 30 days), or in variable calendar months and years?

IMO a dedicated Age function should be added, that determines e.g. the age of a person properly. This function must return exactly zero months and days for dates of the same month and day (birthday), regardless of the years. Looks easy to accomplish, but is not really :-(

DoDi

Not with ISO date format (yyyy-mm-dd), where a single string compare is sufficient. But certainly a string compare is more expensive than a comparison of two Real values.

DoDi

Of course.
Even the proposed type-casting of such records to large integers would make it easy to compare two such dates (at least to decide which one is older).

But the original question was about other (diff) functions for dates in yyyy, mm.. format and Michael's proposal was to convert such dates into floats on the fly which I found to be a performance hit.

It seems that two different topics are discussed here: 1.) What is the best format to store dates
2.) *If* format yyyy, mm, dd, mm, ss... was chosen do date functions exist that deal with it

The only solution to 2.) I have read so far was to convert into float date format and use the existing functions for this format which IMO has a performance issue. Function that take yyyy,mm,dd,mm,ss,... format directly seem not to exist (in standard libraries).

John Landmesser wrote

Result would be: 0 years, 0 moths, 11 days

IMHO a date diff in this format us desperately misleading, as the count of days in a month varies.

-Michael

understood but it is what many of us want for tasks of this nature... it means that one need know the numbers of days in the months between the dates so they can be properly counted... it also means knowing if a leap year is involved at some point...

the *Span routines may be able to give us this info since they have fractional parts but as the code notes, starting with years or months values will result in an estimation...

.. still struggeling with a function DateDif( ANow, AThen: TDateTime) : TDateTime

We have the functions YearsBetween, MonthsBetween, DaysBetween and
YearSpan, MonthSpan, DaySpan

Test is: ANow := '01.01.2000'
             AThen := '01.08..2001'

Result must(!!) be 1 year , 7 months, 0 days!!

I don't get it using mentioned functions?!!

I'll try again later :-((

That's what i realized until now::

Quote of lhelp:
"MonthsBetween returns the number of whole months between ANow and AThen. This number is an approximation, based on an average number of days of 30.4375

per month (average over 4 years). This means the fractional part of a month is dropped."

MonthsBetween('01.01.2000', '01.03.2000') = 1(!!)

This wrong(!!) result is according to the explanation in the lhelp file, but this function is useless if you have leapyears in your period.

Fazit:

You can't write a DateDif function with the functions in DateUtils.pas ?!!

Giving up and using the Jedi DateDif function.

John Landmesser wrote

Giving up and using the Jedi DateDif function.

What is the problem with that-

IMHO such extraordinary functions should be provided by such highly specialized libraries.

-Michael

The result is correct.

The number of elapsed DAYS between these 2 dates is 60. If the average number of days per month is assumed to be 30.4375, then 2 full months would be 60.875 days.

That means that 60 days DOES NOT span 2 full months of 30.4375 days: it falls 0.875 days short for that.

Hence, the *intended* result is 1.

Check the output of:

uses sysutils,dateutils;
begin
   Writeln('Days ',DaysBetween(EncodeDate(2000,1,1),EncodeDate(2000,3,1)));
   Writeln('Months : ',MonthsBetween(EncodeDate(2000,1,1),EncodeDate(2000,3,1)));
   Writeln('Days ',DaysBetween(EncodeDate(2000,1,1),EncodeDate(2000,3,2)));
   Writeln('Months : ',MonthsBetween(EncodeDate(2000,1,1),EncodeDate(2000,3,2)));
end.

it is

Days 60
Months : 1
Days 61
Months : 2

which, given the definition, is correct.

Fazit: You can't write a DateDif function with the functions in DateUtils.pas ?!!

You can, but if you want EXACT results in the style of Y years, M months, D days, you cannot calculate that with the functions you are using. You can use daysbetween to calculate the number of days, this is an exact number. Calculating the number of years and months can then be done exact, but is not trivial.

As it states in the documentation the above functions are an approximation based on the above assumption on the average number of days in a month.

One can discuss whether such 'approximate' functions are useful, but that is another discussion entirely. These functions are there for Delphi compatibility.

Given their definition they are correct.

So please stop saying that they are wrong.
You just don't understand their intended use correctly.

Michael.

On 11/11/13, Michael Schnell wrote:

First try at it:
(Only works for correct Gregorian dates).

uses Classes, SysUtils, DateUtils;
type
  TDaysPerMonth = Array[1..12] of Word;
function DaysPerMonth(AMonth: Word; IsLeapYear: Boolean): Word;
const
  DaysPerMonthNormal: TDaysPerMonth = (31,28,31,30,31,30,31,31,30,31,30,31);
  DaysPerMonthLeap:   TDaysPerMonth = (31,29,31,30,31,30,31,31,30,31,30,31);
begin
  if IsLeapYear then
    Result := DaysPerMonthLeap[AMonth]
  else
   Result := DaysPerMonthNormal[AMonth];
end;
procedure DateDiff(Date1, Date2: TDate; out Years, Months, Days: Word);
var
  //Reverse: Boolean;
  Y1, Y2, M1, M2, D1, D2: word;
  procedure SwapDates(var D1, D2: TDate);
  var
    TempD: TDate;
  begin
    TempD := D1;
    D1 := D2;
    D2 := TempD;
  end;
begin
  if (Date1 > Date2) then
  begin
    SwapDates(Date1, Date2);
    //Reverse := True;
  end
  else
    //Reverse := False;
  Days := 0;
  Months := 0;
  Years := 0;
  DecodeDate(Date1, Y1, M1, D1);
  DecodeDate(Date2, Y2, M2, D2);
  Years := Y2 - Y1;
  if (M1 > M2) or ((M1 = M2) and (D1 > D2)) then Dec(Years);
  if (M1 > M2) then Inc(M2, 12); //already adjusted Years in that case
  Months := M2 - M1;
  if (D2 >= D1) then
    Days := D2 - D1
  else
  begin
    //writeln('D2 < D1');
    if (Months = 0) then
      Months := 11
    else
      Dec(Months);
    //writeln('DaysPerMonth(',M1,') = ',DaysPerMonth(M1, IsLeapYear(Y2)));
    Days := (DaysPerMonth(M1, IsLeapYear(Y2)) - D1) + D2 ;
  end;
end;

Bart

John Landmesser wrote

Fazit: You can't write a DateDif function with the functions in DateUtils.pas ?!!

actually, you can but it is more brute-force for the results that you and i are looking for... brute-force as in actually looping thru each unit and incrementing a counter for that part to fill a record of some sort which is then used to show the desired counts... i'm working on one as we speak ;)

Michael Van Canneyt wrote

The number of elapsed DAYS between these 2 dates is 60. If the average number of days per month is assumed to be 30.4375, then 2 full months would be 60.875 days. That means that 60 days DOES NOT span 2 full months of 30.4375 days: it falls 0.875 days short for that. Hence, the *intended* result is 1.

But dates from the 1st of a month to the 1st of the next month should be considered to span a full month, shouldn't it? If not, of what use would it be?
A month is *not* defined to be 30.4375 days.
Getting an average months is completely useless IMO. Who wants to get such a result for what purpose?

Given their definition they are correct. So please stop saying that they are wrong. You just don't understand their intended use correctly.

Then it seems that he meens that the *definition* is wrong and useless.

Like I said: you can argue whether the functions are useful. They are there for Delphi compatibility.

Feel free to provide other functions, I will happily accept them.

Michael.

Michael Van Canneyt wrote

Feel free to provide other functions, I will happily accept them.

I proposed a "solution" in this thread.
It'll give you (at least that was the intention) the years, months, and days between two (Gregorian) dates.

Bart

2013/11/12 Michael Van Canneyt

If I am not mistaken, Bart just provided one :-) I suggest using another more discriminant name instead of DateDiff, something like AgeBetween or ExactDateDiff.

Hi Bart,

i found this in old RxLib ( now Yedi ).

Perhaps it inspires you.

procedure DateDiff(Date1, Date2: TDateTime; var Days, Months, Years: word);
var
   DtSwap: TDateTime;
   Day1, Day2, Month1, Month2, Year1, Year2: word;
begin
   if Date1 > Date2 then
   begin
     DtSwap := Date1;
     Date1 := Date2;
     Date2 := DtSwap;
   end;
   DecodeDate(Date1, Year1, Month1, Day1);
   DecodeDate(Date2, Year2, Month2, Day2);
   Years := Year2 - Year1;
   Months := 0;
   Days := 0;
   if Month2 < Month1 then
   begin
     Inc(Months, 12);
     Dec(Years);
   end;
   Inc(Months, Month2 - Month1);
   if Day2 < Day1 then
   begin
     // von mir auskommentiert Inc(Days, DaysPerMonth(Year1, Month1));
     Inc(Days, DaysInAMonth(Year1, Month1));
     if Months = 0 then
     begin
       Dec(Years);
       Months := 11;
     end
     else
       Dec(Months);
   end;
   Inc(Days, Day2 - Day1);
end;

My Tests showed: this procedure works well !

The next function seems also much better than that in DateUtil:

function MonthsBetween(Date1, Date2: TDateTime): Double;
var
   D, M, Y: Word;
begin
   DateDiff(Date1, Date2, D, M, Y);
   Result := 12 * Y + M;
   if (D > 1) and (D < 7) then Result := Result + 0.25
   else if (D >= 7) and (D < 15) then Result := Result + 0.5
   else if (D >= 15) and (D < 21) then Result := Result + 0.75
   else if (D >= 21) then Result := Result + 1;
end;

Problem: it returns Double!!

I'll use these two and i think everything is ok?!!

happy hacking ...

John

it is amazing what refinements and streamlining will come up with at times... originally this was totally brute force... then i started refining and cleaning it up until i came up with this... it also includes a set of a set of dates for testing...

===== snip =====
program DateDiff3;
uses
   SysUtils,DateUtils,StrUtils;
type
   Date_Diff = record
                 Years,
                 Months,
                 Days: Word;
               end;
function CalendarDateDiff(Date1,Date2: TDateTime): Date_Diff;
var
   theDiffRec: Date_Diff;
   Cmp: Integer;
   loDate,hiDate: TDateTime;
   loYear,hiYear,loMonth,hiMonth,loDay,hiDay: Word;
begin
   FillChar(theDiffRec,SizeOf(theDiffRec),0);  // init results to zero
   Cmp:=CompareDateTime(Date1,Date2);          // compare dates
   If Cmp<0 then
   begin
     loDate:= Date1;                           // and set loDate to oldest
     hiDate:= Date2;
   end
   else if Cmp>0 then
   begin
     loDate:= Date2;                           // and set loDate to oldest
     hiDate:= Date1;
   end;
   DecodeDate(loDate,loYear,loMonth,loDay);
   DecodeDate(hiDate,hiYear,hiMonth,hiDay);
   theDiffRec.Years:= hiYear - loYear;
   if (loMonth > hiMonth) or ((loMonth = hiMonth) and (loDay > hiDay)) then
   begin
     theDiffRec.Years:= theDiffRec.Years - 1;
   end;
   if loMonth > hiMonth then
   begin
     hiMonth:= hiMonth + 12;
   end;
   theDiffRec.Months:= hiMonth - loMonth;
   if hiDay >= loDay then
   begin
     theDiffRec.Days:= hiDay - loDay
   end
   else
   begin
     if theDiffRec.Months = 0 then
     begin
       theDiffRec.Months:= 11;
     end
     else
     begin
       theDiffRec.Months:= theDiffRec.Months - 1;
     end;
     theDiffRec.Days:= DaysInAMonth(hiYear,loMonth) - loDay + hiDay;
   end;
   Result:= theDiffRec;
end;
procedure Test(D1,D2: TDateTime);
var
   DateDiffRec: Date_Diff;
begin
   FillChar(DateDiffRec,SizeOf(DateDiffRec),0);
   DateDiffRec:= CalendarDateDiff(D1,D2);
   writeln(PadLeft(IntToStr(DateDiffRec.Years),4)+' yrs
'+PadLeft(IntToStr(DateDiffRec.Months),4)+' mos
'+PadLeft(IntToStr(DateDiffRec.Days),4)+' days');
end;

type
DateRecord = record
Year,
Month,

                  Day: Word;
                end;

const
TstDates1: array[1..26] of DateRecord = (
(Year:2000;Month:01;Day:01),
(Year:2000;Month:01;Day:02),
(Year:2000;Month:01;Day:31),
(Year:2000;Month:02;Day:01),
(Year:2000;Month:02;Day:28),
(Year:2000;Month:02;Day:29),
(Year:2000;Month:03;Day:01),
(Year:2000;Month:03;Day:15),
(Year:2000;Month:12;Day:31),
(Year:2001;Month:01;Day:01),
(Year:2001;Month:01;Day:02),
(Year:2001;Month:02;Day:01),
(Year:2001;Month:02;Day:28),
(Year:2001;Month:02;Day:29),
(Year:2001;Month:03;Day:01),
(Year:2001;Month:03;Day:15),
(Year:2001;Month:08;Day:01),
(Year:2001;Month:12;Day:31),
(Year:2004;Month:01;Day:01),
(Year:2004;Month:01;Day:02),
(Year:2004;Month:02;Day:01),
(Year:2004;Month:02;Day:28),
(Year:2004;Month:02;Day:29),
(Year:2004;Month:03;Day:01),
(Year:2004;Month:03;Day:15),
(Year:2004;Month:12;Day:31)

                                           );
   TstDates2: Array[1..26] of DateRecord = (
                                            (Year:2000;Month:01;Day:01),
                                            (Year:2000;Month:01;Day:02),
                                            (Year:2000;Month:01;Day:31),
                                            (Year:2000;Month:02;Day:01),
                                            (Year:2000;Month:02;Day:28),
                                            (Year:2000;Month:02;Day:29),
                                            (Year:2000;Month:03;Day:01),
                                            (Year:2000;Month:03;Day:15),
                                            (Year:2000;Month:12;Day:31),
                                            (Year:2001;Month:01;Day:01),
                                            (Year:2001;Month:01;Day:02),
                                            (Year:2001;Month:02;Day:01),
                                            (Year:2001;Month:02;Day:28),
                                            (Year:2001;Month:02;Day:29),
                                            (Year:2001;Month:03;Day:01),
                                            (Year:2001;Month:03;Day:15),
                                            (Year:2001;Month:08;Day:01),
                                            (Year:2001;Month:12;Day:31),
                                            (Year:2004;Month:01;Day:01),
                                            (Year:2004;Month:01;Day:02),
                                            (Year:2004;Month:02;Day:01),
                                            (Year:2004;Month:02;Day:28),
                                            (Year:2004;Month:02;Day:29),
                                            (Year:2004;Month:03;Day:01),
                                            (Year:2004;Month:03;Day:15),
                                            (Year:2004;Month:12;Day:31)
                                           );

var

   DCnt1,DCnt2: Integer;
   DT1,DT2: TDateTime;
   DStr1,DStr2: String;

begin

   writeln('TstDates1 has ',Length(TstDates1),' records');
   writeln('TstDates2 has ',Length(TstDates2),' records');
   for DCnt1:= 1 to Length(TstDates1) do
   begin
     if

IsValidDate(TstDates1[DCnt1].Year,TstDates1[DCnt1].Month,TstDates1[DCnt1].Day) then

     begin
       DT1:=

EncodeDate(TstDates1[DCnt1].Year,TstDates1[DCnt1].Month,TstDates1[DCnt1].Day);

       DStr1:= FormatDateTime('YYYY-MM-DD',DT1);
       for DCnt2:= 1 to Length(TstDates2) do
       begin
         if

IsValidDate(TstDates2[DCnt2].Year,TstDates2[DCnt2].Month,TstDates2[DCnt2].Day) then

         begin
           DT2:=

EncodeDate(TstDates2[DCnt2].Year,TstDates2[DCnt2].Month,TstDates2[DCnt2].Day);

           DStr2:= FormatDateTime('YYYY-MM-DD',DT2);
           write(DStr1+' to '+DStr2+' is ');
           Test(DT1,DT2);                      // do the test
         end
         else
         begin
           writeln(DStr1+' to

'+PadLeft(IntToStr(TstDates2[DCnt2].Year),4)+'-'+AddChar('0',IntToStr(TstDates2[DCnt2].Month),2)+'-'+AddChar('0',IntToStr(TstDates2[DCnt2].Day),2)+' invalid ending date');

         end;
       end;
     end
     else
     begin

writeln(PadLeft(IntToStr(TstDates1[DCnt1].Year),4)+'-'+AddChar('0',IntToStr(TstDates1[DCnt1].Month),2)+'-'+AddChar('0',IntToStr(TstDates1[DCnt1].Day),2)+'
is an invalid starting date');
     end;
     writeln;
   end;
end.
===== snip =====

*FWIW*

you do not need any of the above if you change

Days := (DaysPerMonth(M1, IsLeapYear(Y2)) - D1) + D2 ;

to

       Days := (DaysInAMonth(Y2,M1) - D1) + D2;

this because DaysInAMonth returns the correct number of days and takes leapyear into account automatically... so there's no reason to duplicate existing code with the above type and function ;)

outside of this, what you came up with is almost exactly what i worked my way down to as i was working on my own solution... it took me a while and then when i saw your post, i was astonished at how the mind works at times :LOL:

Which is correct?

Date1 := 29.2.2000
Date2 := 28.02.2001

Your function:
0 Y, 11 M, 27 D

Rxlib ( Jedi ) DateDiff:
0 Y, 11 M, 28 D

Libre Office Calc:
0 Y, 11 M, 30 D

The table - function of Libre Office Calc is called in german DATUMDIF()

Get a calendar and count??

*Jahr 2000*

Januar Tage
Februar 29
M?rz 31 1. Monat
April 30 2. Monat
Mai 31 3. Monat
Juni 30 4. Monat
Juli 31 5. Monat
August 31 6. Monat
September 30 7. Monat
Oktober 31 8. Monat
November 30 9. Monat
Dezember 31 10. Monat

*Jahr 2001*

Januar 31 11. Monat
Februar 28

RxLib is correct !!!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131112/99fec5ea/attachment-0001.html>

Seeing this, I can't help but think that the approximation approach may not be such a bad idea after all :D

Michael.

I did not know about DaysInAMonth.
I just went coding and created that function because I needed it.

I can see it uses the exact kind of logic I did...

Yes, nice.

Hope anyone actually can use this.

Bart

I would actually say that in this particular case the diff is 1 Year... (11 M + (28 days in feb in a non-leapyear = 1M) = 12M = 1 Y.

Bart

[snip]

RxLib is correct !!!

Fixed. (?)

procedure DateDiff(Date1, Date2: TDate; out Years, Months, Days: Word);
var
  Y1, Y2, M1, M2, D1, D2: word;
  procedure SwapDates(var D1, D2: TDate);
  var
    TempD: TDate;
  begin
    TempD := D1;
    D1 := D2;
    D2 := TempD;
  end;
begin
  if (Date1 > Date2) then
    SwapDates(Date1, Date2);
  Days := 0;
  Months := 0;
  Years := 0;
  DecodeDate(Date1, Y1, M1, D1);
  DecodeDate(Date2, Y2, M2, D2);
  Years := Y2 - Y1;
  if (M1 > M2) or ((M1 = M2) and (D1 > D2)) then Dec(Years);
  if (M1 > M2) then Inc(M2, 12); //already adjusted Years in that case
  Months := M2 - M1;
  if (D2 >= D1) then
    Days := D2 - D1
  else
  begin
    //writeln('D2 < D1');
    if (Months = 0) then
      Months := 11
    else
      Dec(Months);
    //writeln('DaysPerMonth(',M1,') = ',DaysPerMonth(M1, IsLeapYear(Y2)));
    Days := (DaysPerMonth(M1, IsLeapYear(Y2)) - D1) + D2 ;
    if (M1 = 2) and (M2 = 2) and IsLeapYear(Y1) then
    begin
      Inc(Days);
    end;
  end;
end;

Bart

John Landmesser wrote

On 12.11.2013 21:01, waldo kitty wrote:

[...]

actually, i have in some cases... let's ask this question and see what your charts and routines return as the result... assuming your format is DD.MM.YYYY...

Date1 := 01.01.2000
Date2 := 01.01.2000

should be 0 (zero), right?

then

Date1 := 01.01.2000
Date2 := 02.01.2000

should be 1 (one), right?

in this case, what i (and bart, too) wrote, we're not counting the starting day but we are counting the ending day... this rule stays in effect throughout the entire process... my head still aches from rummaging about doing this... it was good exercise and one i remember being given on a test many years back to determine one's coding level... at that time, the code was being done in dBase II/III/IV ;)

i will have to dig deeper and see what is crossing things up for count from 29.02.2000 to 28.02.2001... i see bart has posted a fix which i have not yet had a chance to compare with my code or run thru the tests i included in my posting...

the real question is this:

what do we count? the starting day, the ending day, both days or neither day?

then we have another question:

is dayM in monthY a one month difference to dayM in monthX and monthZ? monthX, monthY, monthZ are any three consecutive months in linear order within one year or crossing two years

once the rules are chosen, then we have something to work with...

I vote for this to be accepted into (FP)DateUtils unit.

-L.

[snip]

All this, should be planned to use 64 bit Unix Timestamps already ;) (year 2038 is not that long away)

-L.

I'm just a hobbyist, never went to university to study computer science.

We don't need to invent the wheel again, because others have solutions for that!

Perhaps its solved in " The Art of/Computer Programming/" from Donald E. Knuth?

I don't have such books and i don't know if i'd have the chance to understand such a book?!!

Yesterday i was running windows on this PC and tried MS Visual Studio 2008 Help.
That showed me ( -> DateDiff ) a Tutorial from a MS-India guy that delivered code to calculate DateDiff.

I'm now running Linux but perhaps i reboot later to look again?

Bart delivered code that calculates 29.02.2000 to 28.02.2001 = 1 Year. And that looks reasonable to me!

Bart wrote

I would actually say that in this particular case the diff is 1 Year... (11 M + (28 days in feb in a non-leapyear = 1M) = 12M = 1 Y. Bart

Soon we'll have working DateDiff function and my question will be answered in the end!
Thanks guys!!

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131113/b6596112/attachment.html>

Lukasz Sokol wrote

[snip] All this, should be planned to use 64 bit Unix Timestamps already ;) (year 2038 is not that long away)

agreed but that is dependent on what TDateTime does since it is built on that like other existing time and date routines ;)

at one point i was using int64 instead of word but i got tired of having to continually convert because of existing routines using word... and yeah, i do know that they can roll over and cause problems there...

This is my first post on this mailing list, so please bear with me. I used to do date calculations like this in the '80s when I was writing Real Estate software.

There are two types of days-between-dates calculations: Inclusive and Exclusive.

Generally, the starting day should be considered the first day of the calculation.

With an Inclusive calculation, the ending day is counted. With an Exclusive calculation, the ending day is not counted -- it is considered to be the first day of the following period.

In Real Estate for example, date calculations are Inclusive. So a 1-week rent payment that starts on Monday 1 January 2007 ends on Sunday 7 January 2007.
Therefore an Inclusive DateDiff calculation between 1/01/2007 and 7/01/2007 would return 7 days.
Whereas an Exclusive calculation would return 6 days.

Similarly, when calculating a person's age.
A baby born on 1/01/2007 would be 7 days old on 7/01/2007.

As I recall, Exclusive dates seem to be more applicable to financial calculations.
For example, an Exclusive DateDiff calculation between 1/01/2007 and 1/04/2007 would return 3 months (not 3 months and 1 day).

then we have another question:

This is a topic that has some tricky boundary conditions.

Generally the rule is easy for days 1-27 in every month: The Inclusive DateDiff between day M in month 1 and Day M-1 in month 2 returns 1 month.
e.g. between 28/01/2007 and 27/02/2007.

However the rules get muddy with days 28-31. i.e. the "last few" days of every month.

For example, an Inclusive DateDiff calculation between 29/01/2007 and 28/02/2007 should return 1 month.
In my opinion, an Inclusive calculation
between 30/01/2007 and 28/02/2007 should also return 1 month. And so should an Inclusive calculation between 31/01/2007 and 28/02/2007.

In fact, we used to call these "calendar months" to differentiate them from "other" months that might always be defined as having perhaps 30 days.

So as you see, there is no real hard answer to your question. It probably depend on what you are trying to achieve.

Lex
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131113/4e639460/attachment.html>

i never went to school for any of my computer skills, either... i'm self taught in both hardware and software ;)

We don't need to invent the wheel again, because others have solutions for that!

then why does this thread exist? O:)

Perhaps its solved in " The Art of/Computer Programming/" from Donald E. Knuth? I don't have such books and i don't know if i'd have the chance to understand such a book?!!

i don't know as i do not have that book... but since you have the three routines available, can you please post what they return for the two cases above??

[...]

Bart delivered code that calculates 29.02.2000 to 28.02.2001 = 1 Year. And that looks reasonable to me!

but it is not, not really... 29.02 only comes around once every four years... 29.02.leapyear to 28.02.leapyear+1 is one day short of a year... if you were born on 29.02.1960, have you celebrated your birthday 13 or 53 times? :)

i can, however, agree with the reasoning bart has given above... do you agree with it? is that what you want to see?

which of the following is what you want to see??

2000-02-29 to 2001-02-28 is 0 yrs 11 mos 27 days (original)

2000-02-29 to 2001-02-28 is 0 yrs 11 mos 28 days (bart's fix)

2000-02-29 to 2001-02-28 is 1 yrs 0 mos 0 days (actually desired?)

I'll try to find what the usual way is to handle these problems: c++ Libraries for example?!

I don't have my test app anymore, changed it a lot last days to follow the discussion, but perhaps you have a look at my post from 12.11.2013, 21:56

That depends on the country , you're living, see
http://en.wikipedia.org/wiki/February_29#Births

I'm not the one to decide, but i need a function which provides widely accepted results!
I'll google again.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131113/64a860e1/attachment.html>

NOTE: subject fixed to indicate the actual topic ;)

agreed and as you note later, two different types of the "base"... either "true interval" or "calendar interval"...

agreed...

Similarly, when calculating a person's age. A baby born on 1/01/2007 would be 7 days old on 7/01/2007.

true :)

the boundaries are what we are trying to determine ;)

Generally the rule is easy for days 1-27 in every month: The Inclusive DateDiff between day M in month 1 and Day M-1 in month 2 returns 1 month. e.g. between 28/01/2007 and 27/02/2007.

yes...

However the rules get muddy with days 28-31. i.e. the "last few" days of every month.

yes... in "calendar" counting, a full month is recorded when DoM1 >= EoM2

openOffice 4 seems to agree with you when using "calendar" counting ;)

the first 12 are leapyear and the last six are non-leapyear...

Start End Days Months Months (interval) (calendar)
2000-01-27 2000-02-28 32 1 1 2000-01-28 2000-02-28 31 1 1 2000-01-29 2000-02-28 30 0 1 2000-01-30 2000-02-28 29 0 1 2000-01-31 2000-02-28 28 0 1 2000-02-01 2000-02-28 27 0 0

2000-01-27 2000-02-29 33 1 1 2000-01-28 2000-02-29 32 1 1 2000-01-29 2000-02-29 31 1 1 2000-01-30 2000-02-29 30 0 1 2000-01-31 2000-02-29 29 0 1 2000-02-01 2000-02-29 28 0 0

2001-01-27 2001-02-28 32 1 1 2001-01-28 2001-02-28 31 1 1 2001-01-29 2001-02-28 30 0 1 2001-01-30 2001-02-28 29 0 1 2001-01-31 2001-02-28 28 0 1 2001-02-01 2001-02-28 27 0 0

i think that the existing routines are two variations on "interval" (inclusive and exclusive) and this discussion is about getting "calendar" routines in place... so far we seem to have something close to a working CalendarDateDiff(D1,D2: TDateTime) :)

waldo kitty wrote

> A baby born on 1/01/2007 would be 7 days old on 7/01/2007. true :)

Realy?
That would mean that a baby born on 2007-01-01 10:00 is two days old on 2007-01-02 10:00?
To me this seems wrong because after 24 hours is only one day old. A simple substraction should give the difference (7-1 or 2-1).

Also, a baby born on 2007-01-01 is one year old on 2008-01-01 (not two). It has its first birthday.

i was being facetious thus the smiley ;)

i know that feeling... i've taken to trying to save each in its own project so that i still have them to go back to to look over ;)

yeah, i've heard that, too... however, my experience shows that it is personal preference in many cases... i have several friends born on 29 Feb and some of them count every full year as their age but only celebrate on leapyears while others only count the leapyears... then there's the one oddball who celebrates every year but only counts the leapyears :lol:

as lex edmonds spoke of, there are two types of calculations... inclusive and exclusive where the ending day is counted or not... then there is also whether we are looking to use (strict) intervals or calendar intervals... if i have been keeping up, you are looking for calendar intervals (based on your post where 01.01.2000 to 01.08.2001 should be 1 year, 7 months)...

as far as "not one to decide"... on the contrary... you are the one to decide if a routine fits your purposes... as far as accepting a routine to be added to one of the libraries distributed with FPC and/or Lazarus, yeah, that's up to someone on those teams...

so which one of these are you actually looking for? ;)

Seeing this, I can't help but think that the approximation approach may not be such a bad idea after all :D

Can you elaborate what the approximation is? I fail to see it. 1 julian year = 365.25 days of 86400 SI seconds each. Of course there are lots of other definitions for year but if FPC uses the julian one the value is exact and no approximation. And it is one of the year definitions that make sense as a unit of time (in contrast to calendar year ...)

R.

Ok, i can't read them :--((

as far as "not one to decide"... on the contrary... you are the one to decide if a routine fits your purposes... as far as accepting a routine to be added to one of the libraries distributed with FPC and/or Lazarus, yeah, that's up to someone on those teams...

Background to my question about DateDiff():

I wrote a little app for my Win Box in office:

if i klick on it, windows shuts down; Space & klick reboots and F1 & klick shows me a message box, telling me how long i have to stay in Office to fullfill the 8 h 30 min working time. Now i have my pension, starting 01.08.2018 and i thought: would be nice to know in this message box how many Years, Month, days left?!!

That how the story begins ... and lazarus would be better, if there exists a function DateDiff.

Meanwhile i googled again and learned. 29.01.2000 until 28.01.2001 is 0 Y 11 M and 30 D in Libereoffice and MS Office 360.

I' ll look later in my Win on this computer to look what MS Access 97 tells me.

What about Databases like mysql, Oracle, Firebird. I'm shure they have such a function.

.. lots to search for .. hihi ... my cooking is ready ... see you :-))

from dateutil.inc (line ~64)...

Const
   { Average over a 4 year span. Valid for next 100 years }
   ApproxDaysPerMonth: Double = 30.4375;
   ApproxDaysPerYear: Double  = 365.25;

Of course there are lots of other definitions for year but if FPC uses the julian one the value is exact and no approximation. And it is one of the year definitions that make sense as a unit of time (in contrast to calendar year ...)

the problem is that knowing the (eg) YearsBetween or YearsSpan does not easily lend itself to being able to determine the remainder Months, Days, Hours, Minutes, Seconds... the same with MonthsBetween or MonthSpan... Days is known and can be used for the other finer values but you also cannot use it to build a months or years value that works out properly with the other values...

The approximation is that the calculation is based on a difference in the number of days.

The number of days is then divided by an average number of days per month, in order to get a number of months.

Michael.

John Landmesser wrote

I'm just a hobbyist, never went to university to study computer science.

So am I.
(For my background, see my userpage on the wiki)

We don't need to invent the wheel again, because others have solutions for that!

Of course we don't have to.
I just gor intrigued by the (at that time already) long thread, and since no-one gave a solution, I just went for it and gave it my try.

Just because it is fun to do.

Bart

Bart wrote

On 11/13/13, John Landmesser wrote: > I'm just a hobbyist, never went to university to study computer science. So am I. (For my background, see my userpage on the wiki)

Found your userpage , read it with great interest. Your experience with Lazarus sounds fimiliar to me ( Delphi 5, Delphi 2010 <-- horrible ) and now Lazarus, that fits my needs!

I will use your version of DateDiff, thank you!!!

Perhaps it can become a part of FreePascal, that is missing such a function.

... the end of this thread is near?

.. perhaps.

I got an answer to my question.

Regards,

John

i probably should set something, too... while i have an account on the wiki and have done a little bit of editing, i haven't set much of any background type of information...

a brother from a different mother! howdy, brother! :) O:)

i'm still working on it, too :lol: i had thought i found a spreadsheet that would give the answers so as to compare with my output... the first thing i had to do, though, was set a max(a1,a2) and min(a1,a2) so that the lower date was placed first and the higher second as our code does but while i got the same answers for numerous tests, some returned a negative number in the sheet :(

so besides that, i've been trying to work my way, manually, through and count on a calendar... so far, everything seems to come out but not with the original code you and i posted and not with your fix... i had to go a bit deeper for the corners... anyway, i'm still whacking away at it and hope to maybe get something completed for the community... but it is slow and the columns of numbers just meld together at time when manually counting from the almanac %)

Reimar Grabowski wrote

1 julian year = 365.25 days of 86400 SI seconds each. Of course there are lots of other definitions for year but if FPC uses the julian one the value is exact and no approximation

So the difference between 2007-01-01 12:00 and 2008-01-01 12:00 ist *not* one year? It's only 365,00 days and therefore not a full year according to your definition. But this it totaly wrong.
Same day same time of one year and the next should always be a one year difference.

Hi,

So the difference between 2007-01-01 12:00 and 2008-01-01 12:00 ist *not* one year?

No, the base definition of the year is not a digit change, but the time it take to the Earth to return at the same point of its orbit around the Sun.
This is actually 365.2422 days, and this is named the tropical year. The julian year of 365.25 is a convenient approximation still in use despite the julian calendar was abrogated some 400 years ago.

All this efforts are to bypass the problem with the calendar year (the one you mention) because it is sometime 365 and sometime 366 days. This is a totally unacceptable definition when you need an homogeneous time scale.

So all depend of the use you need for your application and it must be admitted that one set of definition/function is not sufficient and every one must be careful when using time period.

For me the very simple functions as implemented if FPC are sufficient, the most important here is Delphi compatibility. And you need to provide your own if you need something more specific, such writing a calendar application.

Patrick

J?rgen Hestermann wrote

But this it totaly wrong.

As is defining a meter by the count stretched rubber bands: 1 2, 3, 4 or 5 ???? All numbers are true and all are wrong :-) :-) :-)

-Michael

It is not one julian year. There is nothing like a year. As i said there are different definitions. If you talk about calendar year it's length changes and is therefor not a good measure of time and not used in science.

It's only 365,00 days and therefore not a full year according to your definition.

Correct. And this is not my definition it is the correct definition of a julian year and it is based on SI units. Time is measured in SI seconds. If you cannot express 'your year' as seconds it is not a unit of time.

But this it totaly wrong.

Care to elaborate? Or is it just your gut feeling?

Same day same time of one year and the next should always be a one year difference.

According to what definition of year? Calendar year is a rather poor unit to measure time differences as it is not constant and cannot be expressed in seconds.

R.

+1

R.

What about Databases like mysql, Oracle, Firebird. I'm shure they have such a function.

For MySQL:

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 ? expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

:)

R.

El 14/11/2013 7:56, Patrick Chevalley escribi?:

All this efforts are to bypass the problem with the calendar year (the one you mention) because it is sometime 365 and sometime 366 days. This is a totally unacceptable definition when you need an homogeneous time scale.

Hello,

I was following this thread about the years, months, days, datediff and almost all people expect that dates:

2013/05/15 - 2013/07/16

Return 2 months + 1 day, but I expect it to return 1 month and 16+16 days, because month definition out of context is around 30.43.... So in the above example for sure 1 months has been passed and 32 days, then you can decide if 32 days is 1 month plus or not.

The same applies to years:

1999/02/28 - 2000/02/29 = 1 Year
2000/02/27 - 2001/02/28 = 1 Year + 1 day
2000/02/28 - 2001/02/28 = 1 Year
2000/02/29 - 2001/02/28 = 1 Year
2000/02/29 - 2001/03/01 = 1 Year + 1 day

2001/05/2 - 2002/06/30 = 1 Year + 29+30 days

Maybe I'm completly wrong ?

... be carefull!

Databases as MySQL or FirebIrd or Oracle have a buildin function DateDiff, but the result is different!!

"Our" function delivers the age of a person in years, months, days.

The other datediff function delivers years OR month OR days.

So you can't use them to calculate the age of a person for example!!

right but this whole thread was started because someone wanted a calendar based datediff function which returns years, months and days... granted, they could have used SI units of 86400 sec/day and 365.25 day's per year (30.4375 days/month) to calculate the difference between their ending date and the current date in a continuous calculation but then looking at the calendar and counting the ones they could see would be different...

start: 14 Nov 2013
end: 01 Aug 2018

days: 1721

1721 / 365.25 = 4.7118412047 years = 4 years
0.7118412047 * 365.25 = 260 days
260 / 30.4375 = 8.5420944559 months = 8 months
0.5420944559 = 30.4375 = 16.5 days

(trunc) 4 years, 8 months, 16 days
OR
(round) 4 years, 8 months, 17 days

FWIW: the routine we've been working on (with bug fix modifications) returns the same output as the above (from openoffice calc)...

2013-11-14 to 2018-08-01 is 4 yrs 8 mos 17 days

yay! :lol:

by who's definition of "time"? ;) O:)

actually, it can be but depending on the frame reference used, it is either fractions or the count varies ;)

so far we have determined in this thread that there are at least four basic sets of routines...

calendar inclusive
calendar exclusive
fixed inclusive (existing *Span routines)
fixed exclusive (existing *Between routines)

inclusive: includes the first and last days of the period in the count. used in (eg) rent and age calculations.
eg: Mon 01 Jul 2013 -> Fri 05 Jul 2013 = 5 day work week Mon 01 Jul 2013 -> Sun 07 Jul 2013 = 7 day (1 week) rental

exclusive: does not count the last day of the period. used in (eg) financial calculations.
eg: Mon 01 Jul 2013 -> Mon 08 Jul 2013 = 7 days

calendar: the difference between the date in one year and the same date in the next or previous year is 1 year... the same with days in months... the 10th is one month from the 10th of the previous month and one month from the 10th in the next month no matter how many days there are in the months involved...

fixed: SI oriented counting of the length of years, months and days using specific values for each...

The average length of a year in days is:
346.62 : a draconitic year
353,
354,
355 : common years in some lunisolar calendars 354.37 : average length of a year in lunar calendars 365 : a vague year and a common year in many solar calendars 365.24219 : mean tropical year (rounded to 5 places) for epoch 2000 365.2424 : vernal equinox year (rounded to 4 places) for epoch 2000 365.2425 : average length of a year in the Gregorian calendar 365.25 : the average length of a year in the Julian calendar 365.25636 : mean solar year
365.2564 : a sidereal year
365.25689 : a Gaussian year
366 : a leap year in many solar calendars
383,
384
385 : leap years in some lunisolar calendars. 383.9 : leap year in some lunisolar calendars.

Astronomical years count the Julian year as a unit of time, defined as 365.25 days of 86400 SI seconds each with no leap seconds.

A calendar year is an approximation of the Earth's orbital period in a given calendar. Gregorian calendar years (as well as in the Julian calendar) has either 365 (common years) or 366 (leap years) days.

An average Gregorian year is 365.2425 days = 52.1775 weeks = 8765.82 hours = 525949.2 minutes = 31556952 seconds (mean solar, not SI).

A common year is 365 days = 8760 hours = 525600 minutes = 31536000 seconds.

A leap year is 366 days = 8784 hours = 527040 minutes = 31622400 seconds.

The 400-year cycle of the Gregorian calendar has 146097 days and hence exactly 20871 weeks.

months are almost as bad when counting days...

27.212220 : draconic month
27.321582 : tropical month
27.321661 : sidereal month
29.53 : lunar month
29.530589 : long term average synodic month
: used to calculate eclipse cycles
30.4375 : simple 365.25 / 12 months

even using estimations, one can be off quite a bit depending on the length of the period being measured... it can be rather embarrassing when one is approaching the end of a countdown and everyone can easily see errors of a day or more... witness the .5 day error in the calculations at the top of this post...

same thing as openOffice Calc... then, as i mention in my other post in this thread, one has to figure out how to count the years and months which will use either estimations (decimal fractions) or variants (actual numbers of days per month and year)... the big decision is which one to use for whatever accuracy is desired O:)

[...] "Our" function delivers the age of a person in years, months, days.

What is your diff between 31th Jan and 30 March 2013?

Mattias

Jos? Mejuto wrote

Maybe I'm completly wrong ?

you are completely right, IMHO... one simply needs to choose their desired base and level of error that is acceptable for their task's needs...

the initial problem arose because there's no easy way to calc the desired output with the existing routines and a lot of dancing about... spreadsheets and other languages are in the same boat, too, with the routines they offer...

eg: openOffice Calc - no easy way to retain only the fraction of a result so one must dance with subtracting and truncating all the while recalculating the values over and over again... one can elect to use some scratchpad area on the sheet to store some intermittent values...

witness: these formulas from Calc using fractional estimations like existing routines in FPc/Lazarus

[without scratchpad]
total days: =DAYS(A2;B2)
whole years: =INT(C2/365.25) [C2 holds total days value] whole months: =INT((((C2/365.25)-TRUNC(C2/365.25))*365.25)/30.4375) whole days:
=ROUND((((((C2/365.25)-TRUNC(C2/365.25))*365.25)/30.4375)-TRUNC(((((C2/365.25)-TRUNC(C2/365.25))*365.25)/30.4375)))*30.4375)

[with scratchpad]
end date: 08/01/2018 [MM/DD/YYYY in A2] start date: 11/14/2013 [MM/DD/YYYY in B2] days between: =DAYS(A2;B2) [in C2] 1721 real years: =C2/365.25 [in AD2] 4.7118412047 int years: =INT(AD2) [in D3] 4 real months: =((AD2-TRUNC(AD2))*365.25)/30.4375 [in AE2] 8.5420944559 int months: =INT(AE2) [in E2] 8 real days: =((AE2)-TRUNC(AE2))*30.4375 [in AF2] 16.5 int days: =ROUND(AF2) [in F2] 17

answer:
Jedi DateDiff: 0 Year, 1 month, 30 days

It's really difficult according to waldo kitty's post: today 14:10

*****************************************************************

Hihi, i asked a question about a DateDiff function for FreePascal, because i wanted to know: how many years, month, days left to work in Office until my pension begins on 01.08.2018.

.. and where are we now??

2013/11/14 John Landmesser

Right in the middle of a very interesting discussion :-)

Frankly, this list from time to time ...

-1

-Michael
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131114/2d85bedb/attachment.html>

2013/11/14 Michael Schnell

Well, everyone is entitled to his opinions, right, else we would all be doing C :-)

the one i am currently testing returns

2013-01-31 to 2013-03-30 is 0 yrs 1 mos 30 days

FWTW, too ;)

leapyear:
2012-01-31 to 2012-03-30 is 0 yrs 1 mos 30 days

non-leapyear to leapyear:
2011-01-31 to 2012-03-30 is 1 yrs 1 mos 30 days

leapyear to non-leapyear:
2012-01-31 to 2013-03-30 is 1 yrs 1 mos 30 days

non-leapyear thru leapyear to non-leapyear:
2011-01-31 to 2013-03-30 is 2 yrs 1 mos 30 days

leapyear thru non-leapyear to non-leapyear:
2012-01-31 to 2014-03-30 is 2 yrs 1 mos 30 days

the whole of Feb is counted as a month no matter how many days it has...

Hihi, i asked a question about a DateDiff function for FreePascal, because i wanted to know: how many years, month, days left to work in Office until my pension begins on 01.08.2018.

What do you consider as *one* year, month etc., fixed (average) values or calender values?

When you get numbers for the years, months etc. remaining, and you reduce the number of days every day, what would you do when reaching zero days? You decrease months by one, and reset days to *which* amount?

.. and where are we now??

Still before your answer.

DoDi

waldo kitty wrote

you are completely right, IMHO... one simply needs to choose their desired base and level of error that is acceptable for their task's needs...

It's all about definition.
For me I would think that given "today" is the last day of a given month, then "today" + 1 month = the same date on in the next month, or if the next month has fewer day, the last day of the next month. So
- april 30th + 1 month = may 30th
- march 31st + 1 month = may 30th (not june 1st)
Yet all of our "tools" (includimg mine) say march 31st to april 30th = 0 months + 30 days...

Bart

Patrick Chevalley wrote

> So the difference between 2007-01-01 12:00 and 2008-01-01 12:00 ist *not* one year? No, the base definition of the year is not a digit change, but the time it take to the Earth to return at the same point of its orbit around the Sun.

Well, *this* value (earth at the same point) is also varying. When doing astronomical calculations I would definitely not use a fixed value.

The julian year of 365.25 is a convenient approximation still in use despite the julian calendar was abrogated some 400 years ago.

Of what use would it be to use 365.25 days as a representation of a year? You can also use 400 instead. It would be just an arbitrary definition decoupled from calendars. You cannot calculate anything useful with it. Neither calendar dates nor astronomical things. It is just an accademic value and exists only because it is "so easy to use".

All this efforts are to bypass the problem with the calendar year (the one you mention) because it is sometime 365 and sometime 366 days. This is a totally unacceptable definition when you need an homogeneous time scale.

Well, that's just the whole point of this thread: The time scale we are talking about here is *not* homogeneous! Instead, a synchronization with calendar dates is wanted/needed. That has always been a problem with calendar definitons. The rotation of earth around the sun is not a whole-numbered multiple of its rotation around itself but we want a whole number of days (otherwise day time would no longer be related to sunlight). So we synchronize our calendar with the run of the sun by leap days (and leap seconds). But now years and months are no longer a fixed value. Therefore differences must be calculated in a more complex way based on dates and not as a fixed number of (milli)seconds. It's easy to use fixed values for day, month, year but it is of no use when talking about calendars.

So all depend of the use you need for your application and it must be admitted that one set of definition/function is not sufficient and every one must be careful when using time period.

Just give me an example where a fixed value for a month is of use? If you rent an appartement you will definitely not be able to insist on your fixed definition of a month (i.e., when starting on february the 1st). In daily usage the time ranges vary depending on the month you look at.

For me the very simple functions as implemented if FPC are sufficient,

In what context do you use these functions?

right... that's what i meant by "desired base"... eg: calendar, SI notation, Julian, Gregorian, etc...)

For me I would think that given "today" is the last day of a given month, then "today" + 1 month = the same date on in the next month,

agreed...

or if the next month has fewer day, the last day of the next month.

yeah... i'm undecided on that :? (** below) my current code returns the following...

2012-01-29 to 2012-02-29 is 0 yrs 1 mos 0 days
2012-01-29 to 2012-03-01 is 0 yrs 1 mos 3 days
2012-01-29 to 2012-03-31 is 0 yrs 2 mos 2 days
2012-01-29 to 2012-04-30 is 0 yrs 3 mos 1 days
2012-01-29 to 2012-05-31 is 0 yrs 4 mos 2 days
2012-01-29 to 2012-06-30 is 0 yrs 5 mos 1 days
2012-01-29 to 2012-07-31 is 0 yrs 6 mos 2 days
2012-01-29 to 2012-08-31 is 0 yrs 7 mos 2 days
2012-01-29 to 2012-09-30 is 0 yrs 8 mos 1 days
2012-01-29 to 2012-10-31 is 0 yrs 9 mos 2 days
2012-01-29 to 2012-11-30 is 0 yrs 10 mos 1 days
2012-01-29 to 2012-12-31 is 0 yrs 11 mos 2 days

2012-01-31 to 2012-02-29 is 0 yrs 0 mos 29 days**
2012-01-31 to 2012-03-01 is 0 yrs 1 mos 1 days
2012-01-31 to 2012-03-31 is 0 yrs 2 mos 0 days
2012-01-31 to 2012-04-30 is 0 yrs 2 mos 30 days**
2012-01-31 to 2012-05-31 is 0 yrs 4 mos 0 days
2012-01-31 to 2012-06-30 is 0 yrs 4 mos 30 days**
2012-01-31 to 2012-07-31 is 0 yrs 6 mos 0 days
2012-01-31 to 2012-08-31 is 0 yrs 7 mos 0 days
2012-01-31 to 2012-09-30 is 0 yrs 7 mos 30 days**
2012-01-31 to 2012-10-31 is 0 yrs 9 mos 0 days
2012-01-31 to 2012-11-30 is 0 yrs 9 mos 30 days**
2012-01-31 to 2012-12-31 is 0 yrs 11 mos 0 days

i know what you mean ;) :)

exactly! the earth does not travel in a perfect circle around the sun with the sun in a fixed central position... then there's the tilt of the earth and that the poles wobble around following the 25770 year equinox precession such that Deneb will be the north pole star instead of Polaris at some point in the future (AD10000) :)

When doing astronomical calculations I would definitely not use a fixed value.

but many do use fixed fractional average numbers to make the math easier and they live with the small errors... witness my long posting earlier today with the charts of days per year values and the numerous year periods ;)

i can agree with this ;)

i couldn't have said it better :)

one can see this in recurring billings... some are 30 billing periods whereas others are monthly based where the bill is due on the same day of each month... in the former case, the due date floats about in each successive month... the latter is always on the X day of the month... i much prefer monthly billing over 30 day billing ;)

J?rgen Hestermann wrote

Am 2013-11-14 07:56, schrieb Patrick Chevalley:

[...]

You know what they say about statistics: there are lies, big lies and there is statistics.

Statistics only works if you have large enough data set to calculate the model from... (and /similarly/ or same large data set to extrapolate from, thereafter)

(http://en.wikipedia.org/wiki/Gregorian_calendar is a fascinating read :] )

And for what it's worth, the fraction-based calculations should be then far less [time...] consuming, to just add/subtract/multiply/divide the fractions, than counting the days/hours/mintues/secods one by one... (Only, it needs to be done right :> and the leap year/leap month/30-31day exceptions observed when it comes to the dates: 1 day is at least 0.00273790700 of an average year as in 1/365.2425 - keep too little of this fraction or even worse: corrupt it, and you're in for a bad result...)

Problems may arise with improper rounding up or down, and floating point bugs (assuming the fraction part of TDateTime to be miliseconds / MilisecondsPerDay : Pentium FPU anybody?)

On the other hand if the calculations were Gregorian year based (365.2425...) it'll now be good enough for calculating all western calendar dates since 15/10/1582 :) and to some extent, predict into next century or so.

-L :)

In fact I consider it a waste of bandwidth to discuss a problem that obviously is not solvable at this length. (But who am I do complain about that :-[ .)

-Michael

Michael Schnell wrote

In fact I consider it a waste of bandwidth to discuss a problem that obviously is not solvable at this length. (But who am I do complain about that :-[ .)

Of course it can be solved.
Just add a enough options (appr. 255?) to the procedure to make it behave just like the caller wants.
Do I want to do that? No. ;-)

The fun part for me is the fact that a seemingly simple question, where at first glance you would think "I'll just implement that", can lead to so many "problems".

Bart

Hi list,

This discussion seems to be finished ( 92 posts ) and i want to make a proposal as solution:

Use the function DateDiff from Jedi ( RxLib ) ( JvJCLUtils.pas ).

It's not perfect, but it works for me:

**********************************************

procedure DateDiff(Date1, Date2: TDateTime; var Days, Months, Years: word);
var
   DtSwap: TDateTime;
   Day1, Day2, Month1, Month2, Year1, Year2: word;
begin
   if Date1 > Date2 then
   begin
     DtSwap := Date1;
     Date1 := Date2;
     Date2 := DtSwap;
   end;
   DecodeDate(Date1, Year1, Month1, Day1);
   DecodeDate(Date2, Year2, Month2, Day2);
   Years := Year2 - Year1;
   Months := 0;
   Days := 0;
   if Month2 < Month1 then
   begin
     Inc(Months, 12);
     Dec(Years);
   end;
   Inc(Months, Month2 - Month1);
   if Day2 < Day1 then
   begin
     // von mir auskommentiert Inc(Days, DaysPerMonth(Year1, Month1));
     Inc(Days, DaysInAMonth(Year1, Month1));
     if Months = 0 then
     begin
       Dec(Years);
       Months := 11;
     end
     else
       Dec(Months);
   end;
   Inc(Days, Day2 - Day1);
end;

**********************************************

Hope that statement is usefull for somebody reading this thread.

I couldn't find any bug report on "Project JEDI - Issue Tracker".

Thanks for this discussion!

John
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131116/6a7a716b/attachment-0001.html>

John Landmesser wrote

This discussion seems to be finished ( 92 posts ) and i want to make a proposal as solution: Use the function DateDiff from Jedi ( RxLib ) ( JvJCLUtils.pas ).

that routine is actually a procedure... the one i posted is a function that returns the sought values ;)

you have to feed this one with 5 variables... the one i posted you feed only 3 variables and you get back a three field record... i've been working on also adding 3 more fields for hour, minutes and seconds ;)

plus, the name should be more in line with the actual process... CalendarDateDiff or CalendarDiff...

just my two cents, for what they're worth...

in what way do you think it is not perfect for your purposes or needs?

FWIW: i copied the jedi datediff you posted and placed it in my testing project to compare my output with it... the results are exactly the same...

but i still wonder if the last day of a month to the last day of another month should be counted as a full month...

eg:
CalendarDateDiff: 2012-01-31 to 2012-02-29 = 0 yrs 0 mos 29 days JediDateDiff: 2012-01-31 to 2012-02-29 = 0 yrs 0 mos 29 days

should this actually be

2012-01-31 to 2012-02-29 = 0 yrs 1 mos 0 days

typo! "feed only 2"

variables and you get back a three field record... i've been working on also adding 3 more fields for hour, minutes and seconds ;)

I added a modified version of your implementation to the DateUtils unit as:

Procedure PeriodBetween(Const ANow, AThen: TDateTime; Out Years, months, days : Word);

I chose this name because it is more in line with the existing XXXBetween functions.

I did change the algorithm so it does not do the correction for february of the leap year. It returns now the same results as the Jedi function. (which I didn't take for license reasons)

Thanks for providing an implementation.

Michael.

A suggestion if I may: given the wide variation of possibilities of interpretation, some comments in the code about how the calculation is supposed to work would be very much appreciated, I'm sure. (Of course, that applies even more to added documentation).

Just wanted to avoid another huge thread discussing PeriodBetween in future... as well as confusion on the part of possible users in general.

Thanks,
Reinier

When documenting, all will be explained.

I think it's fairly simple, really.

Start with D1. Call this D.
Keep adding 1 to year of D till your jumps over D2. If you jumped over, substract 1 from year.
Keep adding 1 to month of D till D jumps over D2.
If you jumped over, substract 1 from month.
Keep adding 1 to day of D till D reaches D2.

The number of years, months, days you thus added is the period. I may fiddle a bit with the definition of "jumps over" and "reaches", but essentially that is it.

Michael.

On 11/16/2013 11:07 AM, waldo kitty wrote:
[...]

to this end, i now have my routine doing this... note *'s below...

CalendarDateDiff: 2012-01-31 to 2012-01-31 = 0 yrs 0 mos 0 days JediDateDiff: 2012-01-31 to 2012-01-31 = 0 yrs 0 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-02-29 = 0 yrs 1 mos 0 days* JediDateDiff: 2012-01-31 to 2012-02-29 = 0 yrs 0 mos 29 days

CalendarDateDiff: 2012-01-31 to 2012-03-31 = 0 yrs 2 mos 0 days JediDateDiff: 2012-01-31 to 2012-03-31 = 0 yrs 2 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-04-30 = 0 yrs 3 mos 0 days* JediDateDiff: 2012-01-31 to 2012-04-30 = 0 yrs 2 mos 30 days

CalendarDateDiff: 2012-01-31 to 2012-05-31 = 0 yrs 4 mos 0 days JediDateDiff: 2012-01-31 to 2012-05-31 = 0 yrs 4 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-06-30 = 0 yrs 5 mos 0 days* JediDateDiff: 2012-01-31 to 2012-06-30 = 0 yrs 4 mos 30 days

CalendarDateDiff: 2012-01-31 to 2012-07-31 = 0 yrs 6 mos 0 days JediDateDiff: 2012-01-31 to 2012-07-31 = 0 yrs 6 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-08-31 = 0 yrs 7 mos 0 days JediDateDiff: 2012-01-31 to 2012-08-31 = 0 yrs 7 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-09-30 = 0 yrs 8 mos 0 days* JediDateDiff: 2012-01-31 to 2012-09-30 = 0 yrs 7 mos 30 days

CalendarDateDiff: 2012-01-31 to 2012-10-31 = 0 yrs 9 mos 0 days JediDateDiff: 2012-01-31 to 2012-10-31 = 0 yrs 9 mos 0 days

CalendarDateDiff: 2012-01-31 to 2012-11-30 = 0 yrs 10 mos 0 days* JediDateDiff: 2012-01-31 to 2012-11-30 = 0 yrs 9 mos 30 days

CalendarDateDiff: 2012-01-31 to 2012-12-31 = 0 yrs 11 mos 0 days JediDateDiff: 2012-01-31 to 2012-12-31 = 0 yrs 11 mos 0 days

i have not received Michael's post quoted above... was it posted to the list or only in private to a few individuals?

After all, what would the optimum solution?

waldo kitty wrote

plus, the name should be more in line with the actual process... CalendarDateDiff or CalendarDiff...

Yes, that sounds good.
It makes it clear that the calculation is different to diff-function that use average values.

That sounds very reasonable and is actually the rationale behind this kind of calendar diff function. I don't thing that anything needs to be changed to this algorithm.

So from 28.2. of a year to 29.2. of the next (leap) year is then 1 year plus 1 day and from 29.2. to 28.2. is not a full year (11 months + 28 days)! That's how it would be defined and I can't think of anything that makes more sense here.

Michael Van Canneyt wrote

I think it's fairly simple, really. ...

This does make some sense, even for me :-)

-Michael

2013/11/18 Michael Schnell

I ask in advance all those who thought that this thread was finally dead to excuse me.

I have been working on this since the beginning. Date calculations is a subject I like, I love the quirks we have to use to make them right.

First of all, I decided to use a different name. DateDiff comes from Excel, this is Lazarus, we should try to use names consistent with our functions. I chose DatesToAge, but I am not convinced this name is any better to any other name which has been used in this thread. I'll keep it here as this allows to distinguish my routine from others.

Since the beginning, I felt that there was something missing in how we were tackling the problem: there were dark spaces in what the result should be. Unless I missed something, nobody defined a good way to tell if a result was correct or not. I felt we needed a way to check the calculations in a loop. I decided to use the IncXXX functions to do this. This has 2 advantages IMO: this would rely on preexisting functions (no need to add a new function for performing the check), and it allows to define what the datediff routine does in a simple way: DateDiff is the reverse function of IncYear+IncMonth+IncDay. It also should allow some mathematical tricks like DatesToAge (date1, date3) = DatesToAge (date1, date2) + DatesToAge (date2, date3) (kind of associativity). This has a consequence: the result is exclusive, but if you need the inclusive result, just send date2+1 instead of date2.

I wanted to avoid brute force, so I started with the Jedi code. I first tried to incrementally correct the code, bad idea, of course, all I ended up with was useless messy code. Then I used a spreadsheet to try to find out what the desirable steps were. I did find some of them, but I kept stumbling on other problems further on in my test loop. After one week of this (I only spent my free hours, around 2 hours a day, but still...) I decided it was time to quit. Michael's post convinced me that brute force had something. So I fell back to another algorithm: calculate an estimation of the result, then use a loop to correct it. Still more efficient than brute force. I finally reached the code which follows.

This implementation has been tested with date1 from 2001-01-01 to 2005-12-31 and date2 varying from date1 - 5 years to date1 + 5 years. Overkill, given the algorithm, but that was the test loop I had for my previous test and I kept it. I first define an IncDate function. This combines IncYear, IncMonth and IncDay, ensuring that the combination is done in the "corrrect" order: first add years, then add months, then add days, because the result could be different if you add days first, for example. It should be relatively easy to remove the swapping step from the previous implementations. DatesToAge would then work both ways and if someone needed a unsigned version, one could easily swap the parameters before calling DatesToAge. For this, I'd remove the swapping step and correct the existing code to handle negative values. But I won't try this if nobody is interested in my version :-)

uDateDiff.pas :
unit uDateDiff;
{$mode objfpc}{$H+}
interface
uses
  Classes, SysUtils, Forms, Controls, StdCtrls, DateUtils;
type
  { TForm1 }
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end {TForm1} ;
var
  Form1: TForm1 ;
implementation
{$R *.lfm}
// This function ensures that adding parts to a date is done in the
"correct" order: years first, days last
function  IncDate (const aDate : TDate ; const Years, Months, Days:
integer) : TDate ;
begin { IncDate }
  result := IncDay (IncMonth (IncYear (aDate, years), months), days)
end { IncDate } ;
procedure DatesToAge (Date1, Date2: TDate ; out Years, Months, Days:
integer);
var
  d1, d2: word;
  month2_after_carry : integer ;
  y1, y2, m1, m2: word;
  yw, mw, dw : word ; // work date for intermediate calculations
  temp_date: TDate;
begin {DatesToAge}
  if Date1 > Date2
    then begin {SwapDates}
      temp_date := Date1;
      Date1 := Date2;
      Date2 := temp_date;
    end {SwapDates};
  Days := 0;
  Months := 0 ;
  Years := 0 ;
  DecodeDate (date1, y1, m1, d1) ;
  DecodeDate (date2, y2, m2, d2) ;
  if d2 >= d1
    then month2_after_carry := m2
    else month2_after_carry := m2 - 1 ;
  if month2_after_carry >= m1
    then begin
      Years := y2 - y1 ;
      Months := month2_after_carry - m1
    end {then}
    else begin
      Years := y2 - y1 - 1 ;
      Months := month2_after_carry - m1 + 12
    end {else} ;
  if d2 >= d1
    then Days := d2 - d1
    else begin
      DecodeDate (IncMonth (date2, -1), yw, mw, dw) ;
      Days := d2 - d1 + DaysInAMonth (yw, mw)
    end {else} ;
  while IncDate (date1, Years, Months, Days) < Date2 do Inc (Days) ;
  while IncDate (date1, Years, Months, Days) > Date2 do Dec (Days) ;
  if IncDate (date1, Years, Months, 0) = DaysInAMonth (y1+Years, m1+Months)
    then begin
      Days := d1 - DaysInAMonth (y1+Years, m1+Months) ;
      if Months = 12
        then begin
          Inc (Years) ;
          Months := 1
        end {then}
        else Inc (Months)
    end {then}
end {DatesToAge} ;

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  years, months, days: integer;
  day_count_1, day_count_2: integer ;
  date1, date2, date3 : TDate ;
  test_ok : boolean ;
begin {TForm1.Button1Click}
  Memo1.Clear ;
  test_ok := TRUE ;
  for day_count_1 := 0 to DaysBetween (EncodeDate (2000, 01, 01),
EncodeDate (2005, 12, 31)) do begin
    date1 := EncodeDate (2000, 01, 01) + day_count_1 ;
    for day_count_2 := 0 to (365*5) do begin
      date2 := IncDay (date1, day_count_2) ;
      DatesToAge(date1, date2, years, months, days) ;
      date3 := IncDate (date1, years, months, days) ;
      if (date3  date2)
        then begin
          test_ok := FALSE ;
          Memo1.Append ('------'+DateToStr (date1)+' '+DateToStr (date2)+'
Y='+IntToStr (years)+' M='+IntToStr (months)+' D='+IntToStr (days)+'
'+DateToStr (date3));
          Memo1.Append ('incYear='+DateToStr (IncYear (date1, Years))+',
IncMonth+IncYear='+DateToStr (IncMonth (IncYear (date1, years), months)));
          exit
        end {then}
    end {for}
  end {for} ;
  if test_ok
    then Memo1.Append ('Finished OK')
    else Memo1.Append ('Finished KO')
end {TForm1.Button1Click} ;

end {uDateDiff}.

uDateDiff.lfm :

object Form1: TForm1
Left = 1397
Height = 106
Top = 35
Width = 501
Caption = 'Form1'
ClientHeight = 106
ClientWidth = 501
LCLVersion = '1.2.0.1'
object Button1: TButton
Left = 8
Height = 25
Top = 8
Width = 75
Caption = 'Button1'
OnClick = Button1Click
TabOrder = 0

  end
  object Memo1: TMemo
    Left = 88
    Height = 88
    Top = 8
    Width = 406
    Anchors = [akTop, akLeft, akRight, akBottom]
    ScrollBars = ssAutoBoth
    TabOrder = 1
  end

end

Frederic Da Vitoria wrote

procedure DatesToAge (Date1, Date2: TDate ; out Years, Months, Days: integer); var

Hi Frederic,

your code works as aspected!

Perhaps it's more "usual" if you change the "out" Parameters to word?!

Regards

John

2013/11/18 John Landmesser

Yes, it would be more usual, but since you seem interested, I will try to create a "signed version, which will of course return signed values.

Maybe it's better to use an additional boolean parameter (named "negative"?) to indicate that the dates are reversed and keep the other values all positive.
Otherwise it would be difficult to find out that the dates are interchanged because you have to check *all* other integer values as some may be zero. -------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131118/317694c3/attachment.html>

Frederic Da Vitoria wrote

First of all, I decided to use a different name. DateDiff comes from Excel, this is Lazarus, we should try to use names consistent with our functions. I chose DatesToAge, but I am not convinced this name is any better to any other name which has been used in this thread. I'll keep it here as this allows to distinguish my routine from others.

I still find "CalenderDiff" the best name for this function because it clearly states that differences are calculated for calender dates and not for an homogeneous stream of seconds/hours/days.

I still find "CalenderDiff" the best name for this function because it clearly states that differences are calculated for calender dates and not for an homogeneous stream of seconds/hours/days.

This raises immediately the next question: which calendar?

DoDi

Il 18/11/2013 17:46, Hans-Peter Diettrich ha scritto:

I'd say the same used for TDateTime conversions, such as DecodeDate. How many other calendars does fpc currently support in dateutils unit?

Giuliano

:choke: :splutter: :lol: you are right, though... GregorianCalendarDiff then?? but wouldn't that also need corresponding name changes to such routines as DaysIn* and others? or an additional parameter indicating which calendar is to be used? ;) O:)

If you ask yourself this question it is already a good name because it lets you look for documentation.
Much worse are function names that mislead the user by provoking false assumptions.

My point simply is the "calendar" in the name, which suggests to me something more powerful than only an Gregorian calendar (similar to the Encoding of the new AnsiStrings).

DoDi

Western Gregorian ;)

-L.

2013/11/18 J?rgen Hestermann

Here are the latest (and I hope final) versions. I finally managed to reach correct results, even if the code is a bit clunky for my taste.

"DatesToAge" can be changed to "CalendarDiff" (or
"WesternGregorianCalendarDiff" :-) ), I don't care, I have kept "DatesToAge" until now because I felt it made discussions about the different implementations clearer (one could easily miss the difference between "CalendarDiff" and "CalendarDateDiff")

I have tried to give statistics of the differences in results with waldo kitty's function, but the differences are too wide, the version I fetched from this thread is obviously buggy. I'll try again after updating from svn, but I can't do it here (at work), this will have to wait until this evening.

//////////////////////////// uDateDiff.pas:
unit uDateDiff;
{$mode objfpc}{$H+}
interface
uses
  Classes, SysUtils, Forms, Controls, StdCtrls, DateUtils;
type
  { TForm1 }
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;
var
  Form1: TForm1;
implementation
{$R *.lfm}
// This function ensures that adding parts to a date is done in the correct
order: years first, days last
// It is not called by DatesToAge, but is the reference used to check
DatesToAge's results.
function  IncDate (const aDate : TDate ; const Years, Months, Days:
integer) : TDate ;
begin { IncDate }
  result := IncDay (IncMonth (IncYear (aDate, years), months), days)
end { IncDate } ;
// Gives the age in months between Date1 and Date2.
// abs (Months) will always be <= 12
// abs (Days) will always be <= 31
// if Date1 < Date2, Years, Months and Day will all be >= 0
// if Date1 > Date2, Years, Months and Day will all be <= 0
procedure DatesToAge (Date1, Date2 : TDate ; out Years, Months, Days :
integer) ;
var
  day1, day2, year1, year2, month1, month2 : word ;
  work_year, work_month, work_day : word ; // work date for intermediate
calculations
  procedure ProcessPositive ;
  var
    month2_after_carry : integer ;
  begin {ProcessPositive}
    if day2 >= day1
      then month2_after_carry := month2
      else month2_after_carry := month2 - 1 ;
    if month2_after_carry >= month1
      then begin
        Years := year2 - year1 ;
        Months := month2_after_carry - month1
      end {then}
      else begin
        Years := year2 - year1 - 1 ;
        Months := month2_after_carry - month1 + 12
      end {else} ;
    if day2 >= day1
      then Days := day2 - day1
      else begin
        DecodeDate (IncMonth (date2, -1), work_year, work_month, work_day) ;
        Days := day2 - day1 + DaysInAMonth (work_year, work_month)
      end {else} ;
    // now it's time to adjust the dates
    while IncDate (date1, Years, Months, Days) < Date2 do Inc (Days) ;
    while IncDate (date1, Years, Months, Days) > Date2 do Dec (Days) ;
    if Days >= 28
      then begin // check if result can be optimized
        if Months >= 11
          then begin
            work_year := Years + 1 ;
            work_month := Months + 1 - 12
          end {then}
          else begin
            work_year := Years ;
            work_month := Months + 1
          end {else} ;
        if IncDate (date1, work_year, work_month, 0) <= Date2
          then begin // ok, it works
            Years := work_year;
            Months := work_month ;
            Days := 0 ;
            while IncDate (date1, Years, Months, Days) < Date2 do Inc (Days)
          end {then}
      end {then}
  end {ProcessPositive} ;
  procedure ProcessNegative ; // date2 < date1: result will be negative.
Completely different algorithm from ProcessPositive
  begin {ProcessNegative}
    Years := year2 - year1 ;
    Months := month2 - month1 ;
    if Months < -12
      then begin
        Dec (Years) ;
        Inc (Months, 12)
      end {then}
      else if Months > 0
        then begin
          Inc (Years) ;
          Dec (Months, 12)
        end {then} ;
    Days := day2 - day1 ;
    if Days > 0
      then begin
        Inc (Months) ;
        if Months > 0
          then begin
            Inc (Years) ;
            Dec (Months, 12)
          end {then} ;
        Dec (Days, 30) // approximate value, will be adjusted later
      end {then} ;
    // now it's time to adjust the dates
    while IncDate (date1, Years, Months, Days) < Date2 do Inc (Days) ;
    if Days > 0
      then begin
        Inc (Months) ;
        if Months > 0
          then begin
            Inc (Years) ;
            Dec (Months, 12)
          end {then} ;
        Dec (Days, 28) // approximate value, will be adjusted later
      end {then} ;
    while IncDate (date1, Years, Months, Days) > Date2 do Dec (Days) ;
    if IncDate (date1, Years, Months, 0) = DaysInAMonth (year1+Years,
month1+Months)
      then begin
        Days := day1 - DaysInAMonth (year1+Years, month1+Months) ;
        if Months = 12
          then begin
            Inc (Years) ;
            Months := 1
          end {then}
          else Inc (Months)
      end {then}
  end {ProcessNegative} ;
begin {DatesToAge}
  Days := 0;
  Months := 0 ;
  Years := 0 ;
  DecodeDate (date1, year1, month1, day1) ;
  DecodeDate (date2, year2, month2, day2) ;
  if date1 <= date2
    then ProcessPositive
    else ProcessNegative
end {DatesToAge} ;

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  years, months, days: integer; // result of the DatesToAge procedure
  day_count_1, day_count_2: integer ;
  date1, date2, date3 : TDate ;
  test_ok : boolean ;
begin
    Memo1.Clear ;
    Memo1.Append ('Running...');
    test_ok := TRUE ;
    for day_count_1 := 0 to DaysBetween (EncodeDate (2000, 01, 01),
EncodeDate (2005, 12, 31)) do begin
        date1 := EncodeDate (2000, 01, 01) + day_count_1 ;
        for day_count_2 := -(365*5) to (365*5) do begin
            date2 := IncDay (date1, day_count_2) ;
            DatesToAge(date1, date2, years, months, days) ;
            date3 := IncDate (date1, years, months, days) ;
            if   (date3  date2)
              or (abs (days) > 31)
              or (abs (months) > 11)
              or ((date2 > date1) and ((years < 0) or (months < 0) or (days
< 0)))
              or ((date2 < date1) and ((years > 0) or (months > 0) or (days

0)))

then begin

                test_ok := FALSE ;
                Memo1.Append ('------'+DateToStr (date1)+' '+DateToStr

(date2)+' Y='+IntToStr (years)+' M='+IntToStr (months)+' D='+IntToStr (days)+' '+DateToStr (date3));
Memo1.Append ('IncYear='+DateToStr (IncYear (date1, Years))+', IncMonth+IncYear='+DateToStr (IncMonth (IncYear (date1, years), months)));
Memo1.Append ('IncMonth+IncYear='+DateToStr (IncMonth (IncYear (date1, 3), 12)));
exit

              end {then} ;
        end {for}
    end {for} ;
  if test_ok
    then Memo1.Append('Finished OK')
    else Memo1.Append('Finished KO');

end;

end {uDateDiff}.

//////////////////////////// uDateDiff.lfm:

object Form1: TForm1
Left = 1322
Height = 277
Top = 35
Width = 584
Caption = 'Form1'
ClientHeight = 277
ClientWidth = 584
LCLVersion = '1.2.0.1'
object Button1: TButton
Left = 8
Height = 25
Top = 8
Width = 75
Caption = 'Button1'
OnClick = Button1Click
TabOrder = 0

  end
  object Memo1: TMemo
    Left = 88
    Height = 259
    Top = 8
    Width = 489
    Anchors = [akTop, akLeft, akRight, akBottom]
    ScrollBars = ssAutoBoth
    TabOrder = 1
  end

end