You are here: Foswiki>Tasks Web>Item11003 (05 Jul 2015, GeorgeClark)Edit Attach

Item11003: Spreadsheet TIME* functions do not work properly with dates before 1970.

pencil
Priority: Normal
Current State: Closed
Released In: 2.0.0
Target Release: major
Applies To: Extension
Component: SpreadSheetPlugin
Branches: master
Reported By: RobertGerlach
Waiting For:
Last Change By: GeorgeClark
  • %CALC{"$TIMEDIFF($TIME(1950/1/1), $TIME(2011/1/1), year)"}% returns -38, should be 61.
  • %CALC{"$TIME(1950/1/1)"}% should return a negative number, but it does not.

This patch fixed the issue for me:
diff --git a/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm b/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
index ca90e8d..1ea92ff 100644
--- a/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
+++ b/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
@@ -845,7 +845,7 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
     }
     elsif ( $theFunc =~ /^(FORMATTIME|FORMATGMTIME)$/ ) {
         my ( $time, $str ) = split( /,\s*/, $theAttr, 2 );
-        if ( $time =~ /([0-9]+)/ ) {
+        if ( $time =~ /(-?[0-9]+)/ ) {
             $time = $1;
         }
         else {
@@ -922,8 +922,8 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
         $time  = 0  unless ($time);
         $value = 0  unless ($value);
         $scale = "" unless ($scale);
-        $time  =~ s/.*?([0-9]+).*/$1/o      || 0;
-        $value =~ s/.*?(\-?[0-9\.]+).*/$1/o || 0;
+        $time  =~ s/.*?(-?[0-9]+).*/$1/o      || 0;
+        $value =~ s/.*?(-?[0-9\.]+).*/$1/o || 0;
         $value *= 60            if ( $scale =~ /^min/i );
         $value *= 3600          if ( $scale =~ /^hou/i );
         $value *= 3600 * 24     if ( $scale =~ /^day/i );
@@ -939,8 +939,8 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
         $scale ||= '';
         $time1 = 0 unless ($time1);
         $time2 = 0 unless ($time2);
-        $time1 =~ s/.*?([0-9]+).*/$1/o || 0;
-        $time2 =~ s/.*?([0-9]+).*/$1/o || 0;
+        $time1 =~ s/.*?(-?[0-9]+).*/$1/o || 0;
+        $time2 =~ s/.*?(-?[0-9]+).*/$1/o || 0;
         $result = $time2 - $time1;
         $result /= 60            if ( $scale =~ /^min/i );
         $result /= 3600          if ( $scale =~ /^hou/i );
@@ -1436,8 +1436,10 @@ m|([0-9]{1,2})[-\s/]+([A-Z][a-z][a-z])[-\s/]+([0-9]{4})[-\s/]+([0-9]{1,2}):([0-9
         $day  = $1;
         $mon  = $mon2num{$2} || 0;
         $year = $3;
-        $year += 100 if ( $year < 80 );    # "05"   --> "105" (leave "99" as is)
-        $year -= 1900 if ( $year >= 1900 );    # "2005" --> "105"
+       if ($year < 100) {
+           $year += 2000 if ( $year < 80 );    # "05"   --> "2005"
+           $year += 1900 if ( $year >= 80 );   # "85"   --> "1985"
+       }
     }
     elsif ( $theText =~
 m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.]([0-9]{1,2})[-\:/\.]([0-9]{1,2})|
@@ -1445,7 +1447,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     {
 
         # "2003/12/31 23:59:59", "2003-12-31-23-59-59", "2003.12.31.23.59.59"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
         $hour = $4;
@@ -1458,7 +1460,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     {
 
         # "2003/12/31 23:59", "2003-12-31-23-59", "2003.12.31.23.59"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
         $hour = $4;
@@ -1467,7 +1469,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     elsif ( $theText =~ m|([0-9]{4})[-/]([0-9]{1,2})[-/]([0-9]{1,2})| ) {
 
         # "2003/12/31", "2003-12-31"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
     }
@@ -1478,8 +1480,10 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
         $year = $3;
         $mon  = $1 - 1;
         $day  = $2;
-        $year += 100 if ( $year < 80 );    # "05"   --> "105" (leave "99" as is)
-        $year -= 1900 if ( $year >= 1900 );    # "2005" --> "105"
+       if ($year < 100) {
+           $year += 2000 if ( $year < 80 );    # "05"   --> "2005"
+           $year += 1900 if ( $year >= 80 );   # "85"   --> "1985"
+       }
     }
     else {
 

-- RobertGerlach - 27 Jul 2011

See also https://github.com/foswiki/SpreadSheetPlugin/pull/1 for fix and unit tests.

-- GeorgeClark - 18 Aug 2014
 

ItemTemplate edit

Summary Spreadsheet TIME* functions do not work properly with dates before 1970.
ReportedBy RobertGerlach
Codebase 1.1.9, 1.1.8, 1.1.7, 1.1.6, 1.1.5, 1.1.4, 1.1.3, trunk
SVN Range
AppliesTo Extension
Component SpreadSheetPlugin
Priority Normal
CurrentState Closed
WaitingFor
Checkins distro:291ba7fcbc52 distro:ccb0cad41b86
TargetRelease major
ReleasedIn 2.0.0
CheckinsOnBranches master
trunkCheckins
masterCheckins distro:291ba7fcbc52 distro:ccb0cad41b86
ItemBranchCheckins
Release01x01Checkins
Topic revision: r7 - 05 Jul 2015, GeorgeClark - This page was cached on 30 Jun 2016 - 08:50.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License