Community discussions

 
User avatar
yo1frenchtoast
just joined
Topic Author
Posts: 8
Joined: Thu Jul 09, 2015 3:48 pm
Location: Brittany, France

Calculate the difference between two dates

Wed Mar 15, 2017 12:25 pm

Hello,

I would like to share one of my scripts which be used to calculate duration between 2 dates, in case that you are interested ;)
I made it to calculate downtimes on my netwatch rules for example

note : date1 must be inferior to date2 (working on a simple reversing in case date2 > date1)
2017-03-16 : fixed a bug which was causing minus hours when dayDiff >= 1 but all was less than 24h; another bug fixed with minus minutes and seconds (ex: jan/01/1970 12:30:20 and jan/01/1970 12:20:20 was giving 0 days 00:0-10:00)
       ### calculate diff between two dates - yoan tanguy 2017
       
       # expected date format : month/day/year hours:minutes:seconds (ex: mar/14/2017 09:13:54)
       :global date1
       :global date2
       
       
       # date to array format :
       # m a r / 1 4 / 2 0 1 7     0  9  :  1  3  :  5  4
       # 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
       :local date1month [:pick $date1 0 3]
       :local date1day [:pick $date1 4 6]
       :local date1year [:pick $date1 7 11]
       :local date1hours [:pick $date1 12 14]
       :local date1minutes [:pick $date1 15 17]
       :local date1seconds [:pick $date1 18 20]
       
       :local date2month [:pick $date2 0 3]
       :local date2day [:pick $date2 4 6]
       :local date2year [:pick $date2 7 11]
       :local date2hours [:pick $date2 12 14]
       :local date2minutes [:pick $date2 15 17]
       :local date2seconds [:pick $date2 18 20]
       
       
       # month to decimal converter - https://forum.mikrotik.com/viewtopic.php?t=58674
       :local months ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec");
       :set date1month ([:find $months $date1month -1 ] + 1)
       :set date2month ([:find $months $date2month -1 ] + 1)
       
       
       :global globalDiff 
       :local yearDiff ($date2year - $date1year)
       :local monthDiff ($date2month - $date1month)
       :local dayDiff ($date2day - $date1day) 
       :local hoursDiff ($date2hours - $date1hours)
       :local minutesDiff ($date2minutes - $date1minutes)
       :local secondsDiff ($date2seconds - $date1seconds)
       
       
       # handle diff by converting in seconds, avoid negative hours/minutes/seconds (ex: jan/01/1970 09:00:00, jan/02/1970 08:00:00 must give 0 days 23:00:00 and not 1 days 0-1:00:00)
       # 1 days 23:30:10
       # 1*24*60*60 + 23*60*60 + 30*60 + 10
       # ($dayDiff * 24*60*60) + ($hoursDiff * 60*60) + ($minutesDiff *60) + $secondsDiff
       # ($dayDiff * 86400) + ($hoursDiff * 3600) + ($minutesDiff *60) + $secondsDiff
       :local secondsGlobalDiff
       :set secondsGlobalDiff (($dayDiff * 86400) + ($hoursDiff * 3600) + ($minutesDiff *60) + $secondsDiff)
       :set dayDiff ($secondsGlobalDiff / 86400)
       :set secondsGlobalDiff ($secondsGlobalDiff - ($dayDiff * 86400))
       :set hoursDiff ($secondsGlobalDiff / 3600)
       :set secondsGlobalDiff ($secondsGlobalDiff - ($hoursDiff * 3600))
       :set minutesDiff ($secondsGlobalDiff / 60)
       :set secondsGlobalDiff ($secondsGlobalDiff - ($minutesDiff * 60))
       :set secondsDiff $secondsGlobalDiff
       
       
       # check if date1 is older than date2 to avoid errors in calculation
       if ($yearDiff < 0) do={
           :return "error : date1 should be older that date2 (year check), exiting"
       } else={
           if ($yearDiff = 0) do={
               if ($monthDiff <0) do={
                   :return "error : date1 should be older that date2 (month check), exiting"
               } else={
                   if ($monthDiff = 0) do={
                       if ($dayDiff < 0) do={
                           :return "error : date1 should be older that date2 (day check), exiting"
                       } else={
                           if ($dayDiff = 0) do={
                               if ($hoursDiff < 0) do={
                                   :return "error : date1 should be older that date2 (hours check), exiting"
                               } else={
                                   if ($hoursDiff = 0) do={
                                       if ($minutesDiff < 0) do={
                                           :return "error : date1 should be older that date2 (minutes check), exiting"
                                       } else={
                                           if ($minutesDiff = 0) do={
                                               if ($secondsDiff < 0) do={
                                                   :return "error : date1 should be older that date2 (seconds check), exiting"
                                               }
                                           }
                                       }
                                   }
                               }
                           }
                       }
                   }
               }
           }
       }          
       
       
       # check if leap years - https://wiki.mikrotik.com/wiki/AutomatedBilling/MonthEndScript
       :local isYear1Leap 0
       :local isYear2Leap 0
       if ((($date1year / 4) * 4) = $date1year) do={
           :set isYear1Leap 1
       }
       if ((($date2year / 4) * 4) = $date2year) do={
           :set isYear2Leap 1
       }
       
       
       # find the right amount of days between 2 months
       :local daysInEachMonth ("31","28","31","30","31","30","31","31","30","31","30","31");
       :local daysInEachMonthLeapYear ("31","29","31","30","31","30","31","31","30","31","30","31");
       :local totalDaysBetweenMonths
       
       # same year; yearDiff = 0 so year1 = year2
       if ($yearDiff = 0 and $monthDiff >= 1) do={
           if ($isYear1Leap = 0) do={         
               for month from=($date1month - 1) to=($date2month - 1) step=1 do={
                   :set totalDaysBetweenMonths ($totalDaysBetweenMonths + [:pick $daysInEachMonth $month])
               }
           }
           if ($isYear1Leap = 1) do={
               for month from=($date1month - 1) to=(($date2month - 1) - 1) step=1 do={
                   :set totalDaysBetweenMonths ($totalDaysBetweenMonths + [:pick $daysInEachMonthLeapYear $month])
               }
           }
       }
       
       # different year, make concatenation of daysInEachMonth arrays first
       :local daysInEachMonthConcatenatedYears
       if ($yearDiff >= 1) do={
       
           for year from=$date1year to=$date2year step=1 do={
               # if leap year, concatenate the right daysInEachMonth array
               if ((($year / 4) * 4) = $year) do={
                   :set daysInEachMonthConcatenatedYears ($daysInEachMonthConcatenatedYears, $daysInEachMonthLeapYear)
               } else={
                   :set daysInEachMonthConcatenatedYears ($daysInEachMonthConcatenatedYears, $daysInEachMonth)
               }
           }
           
           # must add years count 
           for month from=($date1month - 1) to=(($date2month - 1)  + (($yearDiff * 12) - 1)) step=1 do={
               :set totalDaysBetweenMonths ($totalDaysBetweenMonths + [:pick $daysInEachMonthConcatenatedYears $month])
           }
       }
       
       :local globalDaysDiff ($totalDaysBetweenMonths + $dayDiff)
       
       
       # add leading zeros if necessary
       :if ($hoursDiff < 10) do={
           :set hoursDiff ("0" . $hoursDiff)
       }
       :if ($minutesDiff < 10) do={
           :set minutesDiff ("0" . $minutesDiff)
       }
       :if ($secondsDiff < 10) do={
           :set secondsDiff ("0" . $secondsDiff)
       } 
       
       :set globalDiff "$globalDaysDiff days $hoursDiff:$minutesDiff:$secondsDiff"
       :put $globalDiff
Result :
[user@router] > :global date1 "jan/05/2017 10:00:00";:global date2 "may/15/2018 12:30:00";/system script run diffDate 
495 days 02:30:00

[user@router] > :global date1 [/tool netwatch get [find host=192.168.10.5] since]; :global date2 "$[/system clock get date] $[/system clock get time]"; /system script run diffDate                           
0 days 02:18:19
 
msatter
Forum Guru
Forum Guru
Posts: 1287
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Calculate the difference between two dates

Mon Jun 12, 2017 2:03 pm

I want to suggest to takeover this script into the wiki and just a day I was looking exactly this and hoped to find this script there.
Two RB760iGS (hEX S) in series. One does PPPoE and both do IKEv2.
Running:
RouterOS 6.46Beta59 / Winbox 3.20 / MikroTik APP 1.3.7
Having an Android device, use https://github.com/M66B/NetGuard/releases (no root required)
 
jarda
Forum Guru
Forum Guru
Posts: 7604
Joined: Mon Oct 22, 2012 4:46 pm

Re: Calculate the difference between two dates

Mon Jun 12, 2017 3:14 pm

Wouldn't be everything easier with normal date / time format?
viewtopic.php?t=122022
 
msatter
Forum Guru
Forum Guru
Posts: 1287
Joined: Tue Feb 18, 2014 12:56 am
Location: Netherlands / Nīderlande

Re: Calculate the difference between two dates

Mon Jun 12, 2017 3:46 pm

Wouldn't be everything easier with normal date / time format?
viewtopic.php?t=122022
I don't think so because this script produces an duration/interval which is also used in 99% of the rules and in some places we can enter an hard end-date/time. All time calculations are using this format.

I needed the following format 130d01:45:10

As an way of conversion the best is to have the value Unix time and that is the simplest use and then convert back to duration time or normal date/time format.
Two RB760iGS (hEX S) in series. One does PPPoE and both do IKEv2.
Running:
RouterOS 6.46Beta59 / Winbox 3.20 / MikroTik APP 1.3.7
Having an Android device, use https://github.com/M66B/NetGuard/releases (no root required)
 
jarda
Forum Guru
Forum Guru
Posts: 7604
Joined: Mon Oct 22, 2012 4:46 pm

Re: Calculate the difference between two dates

Tue Jun 13, 2017 5:54 pm

Or the mikrotik should implement calculation formulas with the dates into ros script language...
 
darkbreaker
just joined
Posts: 3
Joined: Thu Sep 14, 2017 6:22 pm

Re: Calculate the difference between two dates

Wed Oct 31, 2018 10:53 am

Hello,
here's the modified script by yo1frenchtoast, with some bugs removed and greatly simplified. Key features are:
-any order of dates is acceptable, but the first char.of the result will be "-" if the second date is older.Example:
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date2="oct/30/2017 08:00:00" date1="sep/20/2018 9:30:00"]
-294 d 01:30:00
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date1="oct/30/2017 08:00:00" date2="sep/20/2018 9:30:00"]
294 d 01:30:00

-if any of dates is ommited, the default value "jan/01/1970 00:00:00" will be used instead.Example:
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date1="oct/30/2017 08:00:00"]
-16012 d 08:00:00
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc ]
0 d 00:00:00
-you can specify the difference output format, by setting optional varibale "mode". Possible values are "d" for days, "h" for hours, "m" for minutes and "s" for seconds. Anything else gives the default.Example:
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date1="oct/30/2017 08:00:00" date2="sep/20/2018 09:30:00" mode="h"]
7057
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date2="oct/30/2017 08:00:00" date1="sep/20/2018 09:30:00" mode="h"]
-7057
[admin@test] > :local myfunc [:parse [/system script get datediff source]];:put [$myfunc date1="oct/30/2017 08:00:00" date2="sep/20/2018 09:30:00" mode="m"]
423450

The script code:

### calculate diff between two dates - yoan tanguy 2017, modified by Darkbreaker oct 2018.
       
# expected date format : month/day/year hours:minutes:seconds (ex: mar/14/2017 09:13:54)
# mode can be: "d" for days,"h" for hours, "m" for minutes , "s" for seconds, ommit or put anything else for default format     
       
# date to array format :
# m a r / 1 4 / 2 0 1 7     0  9  :  1  3  :  5  4
# 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
       :local mydate1
       :local mydate2
        :if ([:typeof $date1]="nothing") do={:set mydate1 "jan/01/1970 00:00:00";}
        :if ([:typeof $date2]="nothing") do={:set mydate2 "jan/01/1970 00:00:00";}
        :if ([:len  $date1] <> 20) do={:set  mydate1 "jan/01/1970 00:00:00";} else={:set mydate1 $date1;}    
        :if ([:len  $date2] <> 20) do={:set  mydate2 "jan/01/1970 00:00:00";} else={:set mydate2 $date2;} 

       :local date1month [:pick $mydate1 0 3]
       :local date1day [:pick $mydate1 4 6]
       :local date1year [:pick $mydate1 7 11]
       :local date1hours [:pick $mydate1 12 14]
       :local date1minutes [:pick $mydate1 15 17]
       :local date1seconds [:pick $mydate1 18 20]


       :local date2month [:pick $mydate2 0 3]
       :local date2day [:pick $mydate2 4 6]
       :local date2year [:pick $mydate2 7 11]
       :local date2hours [:pick $mydate2 12 14]
       :local date2minutes [:pick $mydate2 15 17]
       :local date2seconds [:pick $mydate2 18 20]
# month to decimal converter - https://forum.mikrotik.com/viewtopic.php?t=58674
       :local months ("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec");
       :set date1month ([:find $months $date1month -1 ] + 1);
       :set date2month ([:find $months $date2month -1 ] + 1);

#  make sum of all  daysInEachMonth/daysInEachMonthLeapYear  for range of months between dates + days from date itself  
       :local daysInEachMonth ("31","28","31","30","31","30","31","31","30","31","30","31");
       :local daysInEachMonthLeapYear ("31","29","31","30","31","30","31","31","30","31","30","31");
       :local dayDiff 0;
       :local myYear1;
       :local myYear2;
       :local myMonth1;
       :local myMonth2 ;
       :local myDay1;
       :local myDay2 ;
       :local direction "up";
       :if ($date1year > $date2year) do={ :set direction "down";}
       :if ($date1year < $date2year) do={ :set direction "up";}
       :if (($date1year = $date2year) and ($date1month > $date2month)) do={ :set direction "down";}
       :if (($date1year = $date2year) and ($date1month < $date2month)) do={ :set direction "up";}
       
       :if ($direction="up") do={
#if the first is older 
            :set myMonth1 ($date1month);
            :set myMonth2 ($date2month);
            :set myYear1 ($date1year);
            :set myYear2 ($date2year);
            :set myDay1 ($date1day);
            :set myDay2 ($date2day);
       } else={
            :set myMonth1 ($date2month);
            :set myMonth2 ($date1month);
            :set myYear1 ($date2year);
            :set myYear2 ($date1year);
            :set myDay1 ($date2day);
            :set myDay2 ($date1day);
         } 
      :while (($myYear1<$myYear2) or ($myMonth1<$myMonth2)) do={
# check if leap years - https://wiki.mikrotik.com/wiki/AutomatedBilling/MonthEndScript
# make sum of days frome the right  array
            :if ((($myYear1 / 4) * 4) = $myYear1) do={
                     :set dayDiff ($dayDiff+ [:pick $daysInEachMonthLeapYear ($myMonth1-1)]);
            } else={
                     :set dayDiff ($dayDiff+ [:pick $daysInEachMonth ($myMonth1-1)]);
              }
           :set myMonth1 ($myMonth1 + 1);
           :if ((($myMonth1/12)*12)=$myMonth1) do={:set myYear1 ($myYear1 +1); :set myMonth1  1;} 
#           :put ("myMonth1=$myMonth1,myYear1=$myYear1, dayDiff=$dayDiff, direction=$direction");
       }
       :set dayDiff ($dayDiff- $myDay1+$myDay2);
#           :put ("myDay1=$myDay1,myDay2=$myDay2, dayDiff=$dayDiff, direction=$direction");

#calculating differences       
       :local hoursDiff ($date2hours - $date1hours);
       :local minutesDiff ($date2minutes - $date1minutes);
       :local secondsDiff ($date2seconds - $date1seconds);
       :local globalDiff;
       
# handle diff by converting in seconds, avoid negative hours/minutes/seconds (ex: jan/01/1970 09:00:00, jan/02/1970 08:00:00 must give 23:00:00 and not  0-1:00:00)
# 23:30:10
# 23*60*60 + 30*60 + 10
#  ($hoursDiff * 60*60) + ($minutesDiff *60) + $secondsDiff
# ($hoursDiff * 3600) + ($minutesDiff *60) + $secondsDiff
       :local secondsGlobalDiff  (($hoursDiff * 3600) + ($minutesDiff *60) + $secondsDiff)

       :set hoursDiff ($secondsGlobalDiff / 3600)
       :set secondsGlobalDiff ($secondsGlobalDiff - ($hoursDiff * 3600))
       :if ( $hoursDiff<0) do={:set hoursDiff ($hoursDiff * -1); :set direction "down";}

       :set minutesDiff ($secondsGlobalDiff / 60)
       :set secondsGlobalDiff ($secondsGlobalDiff - ($minutesDiff * 60))
       :if ( $minutesDiff<0) do={:set minutesDiff ($minutesDiff * -1); :set direction "down";}

       :set secondsDiff $secondsGlobalDiff
       :if ( $secondsDiff<0) do={:set secondsDiff ($secondsDiff * -1); :set direction "down";}
#       :return ("$date1->$date2, razlika=$dayDiff dana, $hoursDiff:$minutesDiff:$secondsDiff, direction=$direction")

#sign is minus if direction was "down"        
      :local sign ""
      :if ($direction = "down") do={ :set  sign "-";}
#returning result according to mode specified
      :if ($mode="s") do={ :set globalDiff ($sign.($secondsDiff+ ($minutesDiff*60)+($hoursDiff*3600)+($dayDiff*86400)));:return $globalDiff;}
      :if ($mode="m") do={ :set globalDiff ($sign.($minutesDiff+($hoursDiff*60)+($dayDiff*1440)));:return $globalDiff;}
      :if ($mode="h") do={ :set globalDiff ($sign.($hoursDiff+($dayDiff*24)));:return $globalDiff;}
      :if ($mode="d") do={ :set globalDiff ($sign.$dayDiff);:return $globalDiff;}
# add leading zeros if necessary
       :if ($hoursDiff < 10) do={
           :set hoursDiff ("0" . $hoursDiff)
       }
       :if ($minutesDiff < 10) do={
           :set minutesDiff ("0" . $minutesDiff)
       }
       :if ($secondsDiff < 10) do={
           :set secondsDiff ("0" . $secondsDiff)
       }        
      :set globalDiff ("$sign$dayDiff d $hoursDiff:$minutesDiff:$secondsDiff");
      :return $globalDiff;
 
tabraham
just joined
Posts: 16
Joined: Wed Feb 08, 2017 10:18 pm
Location: Germany
Contact:

Re: Calculate the difference between two dates

Tue Jan 01, 2019 5:46 am

Hello could you please validate the code?

I am getting some errors.
:local myfunc [:parse [/system script get date_compare source]]; \
:put [$myfunc date1="dec/31/2018 09:00:00" date2="jan/01/2018 09:30:00"]

The script gets into an infinite loop

:local myfunc [:parse [/system script get date_compare source]]; \
:put [$myfunc date1="jan/01/2018 09:00:00" date2="jan/02/2018 08:30:00"]

The script returns -1 days 00:30:00

Who is online

Users browsing this forum: Google [Bot] and 15 guests