Moodle
  1. Moodle
  2. MDL-14934

Reports/logs download in Excel format shows wrong time.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.8.2, 1.9.10, 2.0
    • Fix Version/s: 1.9.11, 2.0.1
    • Component/s: Administration
    • Labels:
    • Environment:
      CentOS Linux 4.6
    • Database:
      MySQL
    • Difficulty:
      Moderate
    • Affected Branches:
      MOODLE_18_STABLE, MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Rank:
      1064

      Description

      I have identified a problem when I run a log report on student activity in in Moodle: the date/time in the "Display on Page" is different to what I "Download in Excel format".

      I discovered this when a lecturer asked to me find out when a student uploaded his assignment files. The "On page" showed he uploaded on Tuesday 13 May at 12:43am and 12:45am. The Downloaded excel file showed he submitted on 12 May at 14:43 and 14:45. (see attached excel file and screen shot). So I tried running another log for the same student on 19 May, and am getting the same pattern happening - the excel report I download shows minus 10 hours difference to what is displayed on the screen.

      I have opened the Excel download file in both Excel and OpenOffice, with the same result. The other display formats all do not have this problem (page, text and ODS).

      Does the fact that this behaviour can be reproduced in Excel and OO imply that the issue may be in the XLS file itself?

      1. patch.diff
        1 kB
        Michael Clair
      2. patch.txt
        1 kB
        Michael Avelar

        Issue Links

          Activity

          Hide
          Michael Clair added a comment -

          We ran into the same problem in Moodle 1.9.2. I'm attaching a patch which should resolve the problem (the patch was generated in 1.9.2).

          The times in Excel are based on the number of days that have elapsed since 1/1/1900. When this is calculated in Moodle we use the timezone adjusted time. The time provided is adjusted for the user's timezone so it's not using 0 as the base time. It's +/- for the timezone.

          What this patch does is it will have the time in Moodle use a general base 0. We create the time value as it would be at GMT. We then calculate the Excel value and store it. When it is viewed Excel will inherently have the timezone adjustment.

          The time in the Excel file isn't wrong, it's just using the wrong base.

          This doesn't occur in any of the other formats because they use a different mechanism for dealing with dates and time.

          Show
          Michael Clair added a comment - We ran into the same problem in Moodle 1.9.2. I'm attaching a patch which should resolve the problem (the patch was generated in 1.9.2). The times in Excel are based on the number of days that have elapsed since 1/1/1900. When this is calculated in Moodle we use the timezone adjusted time. The time provided is adjusted for the user's timezone so it's not using 0 as the base time. It's +/- for the timezone. What this patch does is it will have the time in Moodle use a general base 0. We create the time value as it would be at GMT. We then calculate the Excel value and store it. When it is viewed Excel will inherently have the timezone adjustment. The time in the Excel file isn't wrong, it's just using the wrong base. This doesn't occur in any of the other formats because they use a different mechanism for dealing with dates and time.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Hi,

          just reviewing the problem... I really think that we should avoid hacking caller scripts with any GMT/timezone/daylight/base stuff and, instead, use the already built-in possibilities of our glorious excellib.

          If I'm not wrong, the write_date() function already does all that base/timezone manipulations and we only have to pass to it straight the GMT date (the one coming from DB without any modification).

          That function is able to perform the 25569 base conversion and to add/substract the needed seconds in order to get the dates in the timezone the user belongs to.

          So, instead of the patch above, that could work, I haven't tested it for various timezones, I'd suggest to, simply, change this in the course/lib.php file:

          <         $excelTime=25569+$log->time/(3600*24);
          <         $myxls->write($row, 1, $excelTime, $formatDate);
          ---
          >         //$excelTime=25569+$log->time/(3600*24);
          >         $myxls->write_date($row, 1, $log->time, $formatDate);
          

          That way, the caller script (course/lib.php) won't perform any calculation and will rely in the abilities of excellib->write_date() that is able to handle all those conversions properly.

          I've tried the modification above, setting my user in various timezones, both the UTC+-X and named ones (Perth/Australia) and I'm getting consistent dates in excel without any modification.

          Can you confirm if the simple change above works for your cases (timezones)? If so, I think we can apply it without problems.

          TIA and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Hi, just reviewing the problem... I really think that we should avoid hacking caller scripts with any GMT/timezone/daylight/base stuff and, instead, use the already built-in possibilities of our glorious excellib. If I'm not wrong, the write_date() function already does all that base/timezone manipulations and we only have to pass to it straight the GMT date (the one coming from DB without any modification). That function is able to perform the 25569 base conversion and to add/substract the needed seconds in order to get the dates in the timezone the user belongs to. So, instead of the patch above, that could work, I haven't tested it for various timezones, I'd suggest to, simply, change this in the course/lib.php file: < $excelTime=25569+$log->time/(3600*24); < $myxls->write($row, 1, $excelTime, $formatDate); --- > //$excelTime=25569+$log->time/(3600*24); > $myxls->write_date($row, 1, $log->time, $formatDate); That way, the caller script (course/lib.php) won't perform any calculation and will rely in the abilities of excellib->write_date() that is able to handle all those conversions properly. I've tried the modification above, setting my user in various timezones, both the UTC+-X and named ones (Perth/Australia) and I'm getting consistent dates in excel without any modification. Can you confirm if the simple change above works for your cases (timezones)? If so, I think we can apply it without problems. TIA and ciao
          Hide
          Marcus Leonard added a comment -

          Hi Eloy,

          Yes, this tests ok on our installation. (Currently 1.9.7+ Build: 20091209)

          Feel free to commit it!

          Cheers,

          • Marcus
          Show
          Marcus Leonard added a comment - Hi Eloy, Yes, this tests ok on our installation. (Currently 1.9.7+ Build: 20091209) Feel free to commit it! Cheers, Marcus
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Fixed for 1.9.x. Will be available in next weekly (or by direct CVS access).

          Thanks and ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Fixed for 1.9.x. Will be available in next weekly (or by direct CVS access). Thanks and ciao
          Hide
          Ratana Lim added a comment -

          Eloy Lafuente (stronk7) suggested fix, while I agreed, does not work on version 1.9.5+ (Build 20090708). That fix produced 0 byte excel file.

          Show
          Ratana Lim added a comment - Eloy Lafuente (stronk7) suggested fix, while I agreed, does not work on version 1.9.5+ (Build 20090708). That fix produced 0 byte excel file.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Yes, Ratana.

          The patch requires at least Moodle 1.9.7+ as far as the write_date() function was recently added to excellib. So previous versions of Moodle (< 1.9.7+) will fail for sure if you apply that patch.

          Upgrading to current 1.9.x weekly is the way to go. Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Yes, Ratana. The patch requires at least Moodle 1.9.7+ as far as the write_date() function was recently added to excellib. So previous versions of Moodle (< 1.9.7+) will fail for sure if you apply that patch. Upgrading to current 1.9.x weekly is the way to go. Ciao
          Hide
          Ewout ter Haar added a comment -

          I just updated to 1.9.8. Whereas before we had a couple of hours difference between the screen and excel output, now we have 8 days and a couple of hours (the excel date is in the future).

          If I change line 659 of course/lib.php to
          $myxls->write($row,1,userdate($log->time, $strftimedatetime),'');
          it seems to work.

          Show
          Ewout ter Haar added a comment - I just updated to 1.9.8. Whereas before we had a couple of hours difference between the screen and excel output, now we have 8 days and a couple of hours (the excel date is in the future). If I change line 659 of course/lib.php to $myxls->write($row,1,userdate($log->time, $strftimedatetime),''); it seems to work.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Wow, 8 days and a couple of hours!

          I remember I tested this with various user timezones and if worked perfectly here.

          Can you, plz, detail here which are the settings for your OS timezone, Moodle timezone (admin) and user timezone? And also... which is the value you've stored in DB ? and what do you get?

          Having all that information will help to try to reproduce the problem, I hope.

          Ciao

          Show
          Eloy Lafuente (stronk7) added a comment - Wow, 8 days and a couple of hours! I remember I tested this with various user timezones and if worked perfectly here. Can you, plz, detail here which are the settings for your OS timezone, Moodle timezone (admin) and user timezone? And also... which is the value you've stored in DB ? and what do you get? Having all that information will help to try to reproduce the problem, I hope. Ciao
          Hide
          Ewout ter Haar added a comment -

          phpinfo gives
          "Olson" Timezone Database Version 0.system
          Timezone Database internal
          Default timezone America/Sao_Paulo

          "date" in the server's shell gives Thu Apr 1 16:52:55 BRT 2010: I guess that means that the server's timezone is BRT

          The Moodle admin timezone (Location / Location Settings / timezone = Server's local time)
          The user's timezone is server local time

          In mdl_config I have
          timezone | 99 |
          forcetimezone | 99 |

          Hope this helps!

          Do you think the $myxls->write($row,1,userdate($log->time, $strftimedatetime),''); workaround is not a good idea? (I have no idea what it does...).

          Show
          Ewout ter Haar added a comment - phpinfo gives "Olson" Timezone Database Version 0.system Timezone Database internal Default timezone America/Sao_Paulo "date" in the server's shell gives Thu Apr 1 16:52:55 BRT 2010: I guess that means that the server's timezone is BRT The Moodle admin timezone (Location / Location Settings / timezone = Server's local time) The user's timezone is server local time In mdl_config I have timezone | 99 | forcetimezone | 99 | Hope this helps! Do you think the $myxls->write($row,1,userdate($log->time, $strftimedatetime),''); workaround is not a good idea? (I have no idea what it does...).
          Hide
          corprew reed added a comment -

          I am encountering the same issues as Ewout (system: mysql, win2k5, 1.9.8 upgraded from 1.9.3) and his fix solved the issue for me as well.

          Default Timezone: America / LosAngeles (the same issue is happening with a server based on the us east coast in the same config)

          The Moodle admin timezone (Location / Location Settings / timezone = Server's local time)
          The user's timezone is server local time

          In mdl_config I have
          timezone | 99 |

          Anything else that might help?

          Show
          corprew reed added a comment - I am encountering the same issues as Ewout (system: mysql, win2k5, 1.9.8 upgraded from 1.9.3) and his fix solved the issue for me as well. Default Timezone: America / LosAngeles (the same issue is happening with a server based on the us east coast in the same config) The Moodle admin timezone (Location / Location Settings / timezone = Server's local time) The user's timezone is server local time In mdl_config I have timezone | 99 | Anything else that might help?
          Hide
          Helen Foster added a comment -

          Ewout and Corprew, thanks for your comments. Reopening this issue for further investigation.

          Show
          Helen Foster added a comment - Ewout and Corprew, thanks for your comments. Reopening this issue for further investigation.
          Hide
          Michael Avelar added a comment -

          I've made a patch for write_date() function for excellib which seems to render the correct date/time in Excel format. This patch makes use of PHP 5.1 functions.

          Show
          Michael Avelar added a comment - I've made a patch for write_date() function for excellib which seems to render the correct date/time in Excel format. This patch makes use of PHP 5.1 functions.
          Hide
          Jonathon Fowler added a comment -

          I have been dissecting this problem and I believe I have found a solution.

          (In my php.ini, "date.timezone = Australia/Brisbane" ie. UTC+10)

          If Moodle's default timezone ($CFG->timezone) is set to "Server's local time" (99), and the user profile timezone is also "Server's local time" (99), then the bug manifests because according to the code in excellib's write_date(), specifically:

          $timezone = get_user_timezone_offset();
          $value =  ((usertime($date) + (int)($timezone * HOURSECS * 2)) / 86400) + 25569;
          
          1. $timezone is 99, because get_user_timezone_offset() returns 99, because get_user_timezone() returns 99
          2. usertime($date) returns $date unmodified because get_user_timezone_offset() returns 99
          3. thus $value is evaluated as: ($date + (int)(99 * HOURSECS * 2)) / 86400) + 25569;

          In this situation, the system timezone offset in seconds must be added to adjust $date and produce a correct value in Excel.

          Explicitly defining a default timezone for Moodle ($CFG->timezone) results in the correct behaviour for the calculations, and the bug disappears. It also disappears if the user profile specifies a specific timezone.

          So, based on all that, this version of excellib's write_date() works correctly for me:

          function write_date($row, $col, $date, $format=null) {
          /// Calculate the internal PEAR format
              $format = $this->MoodleExcelFormat2PearExcelFormat($format);
          /// Convert the date to Excel format
              $timezone = get_user_timezone_offset();
              if ($timezone == 99) {
                  // system timezone offset in seconds
                  $timezone = (int)date('Z');
              } else {
                  $timezone = (int)($timezone * HOURSECS * 2);
              }
              $value =  ((usertime($date) + $timezone) / 86400) + 25569;
          /// Add  the date safely to the PEAR Worksheet
              $this->pear_excel_worksheet->writeNumber($row, $col, $value, $format);
          }
          

          Hope this helps

          Jonathon

          Show
          Jonathon Fowler added a comment - I have been dissecting this problem and I believe I have found a solution. (In my php.ini, "date.timezone = Australia/Brisbane" ie. UTC+10) If Moodle's default timezone ( $CFG->timezone ) is set to "Server's local time" (99), and the user profile timezone is also "Server's local time" (99), then the bug manifests because according to the code in excellib's write_date() , specifically: $timezone = get_user_timezone_offset(); $value = ((usertime($date) + ( int )($timezone * HOURSECS * 2)) / 86400) + 25569; $timezone is 99, because get_user_timezone_offset() returns 99, because get_user_timezone() returns 99 usertime($date) returns $date unmodified because get_user_timezone_offset() returns 99 thus $value is evaluated as: ($date + (int)(99 * HOURSECS * 2)) / 86400) + 25569; In this situation, the system timezone offset in seconds must be added to adjust $date and produce a correct value in Excel. Explicitly defining a default timezone for Moodle ( $CFG->timezone ) results in the correct behaviour for the calculations, and the bug disappears. It also disappears if the user profile specifies a specific timezone. So, based on all that, this version of excellib's write_date() works correctly for me: function write_date($row, $col, $date, $format= null ) { /// Calculate the internal PEAR format $format = $ this ->MoodleExcelFormat2PearExcelFormat($format); /// Convert the date to Excel format $timezone = get_user_timezone_offset(); if ($timezone == 99) { // system timezone offset in seconds $timezone = ( int )date('Z'); } else { $timezone = ( int )($timezone * HOURSECS * 2); } $value = ((usertime($date) + $timezone) / 86400) + 25569; /// Add the date safely to the PEAR Worksheet $ this ->pear_excel_worksheet->writeNumber($row, $col, $value, $format); } Hope this helps Jonathon
          Hide
          Chris Follin added a comment -

          We are having the issue with the Excel dates showing as 8 days in the future on at least one production site and reproduced in development. Jonathon's modification to write_date() seems to fix it.

          Show
          Chris Follin added a comment - We are having the issue with the Excel dates showing as 8 days in the future on at least one production site and reproduced in development. Jonathon's modification to write_date() seems to fix it.
          Hide
          Michael Blake added a comment -

          Eloy,
          Would you please review the proposed fix for for this one and push it through? It's been reported as a problem for a Partner. Thanks!

          Show
          Michael Blake added a comment - Eloy, Would you please review the proposed fix for for this one and push it through? It's been reported as a problem for a Partner. Thanks!
          Hide
          Vicki Dunnam added a comment -

          Has this bug been fixed? I have read several ways to fix it but was not sure which code I should change to make it work correctly. Our server shows server local time and the users have the same time as the server.

          Show
          Vicki Dunnam added a comment - Has this bug been fixed? I have read several ways to fix it but was not sure which code I should change to make it work correctly. Our server shows server local time and the users have the same time as the server.
          Hide
          Eloy Lafuente (stronk7) added a comment -

          Moving this to STABLE backlog, with critical priority maintained. Stay tuned... thanks!

          Show
          Eloy Lafuente (stronk7) added a comment - Moving this to STABLE backlog, with critical priority maintained. Stay tuned... thanks!
          Hide
          Dongsheng Cai added a comment -

          Submitted pull request to merge Jonathon's patch, thanks.

          PULL-16 & PULL-17

          Show
          Dongsheng Cai added a comment - Submitted pull request to merge Jonathon's patch, thanks. PULL-16 & PULL-17
          Hide
          Dongsheng Cai added a comment - - edited

          Thanks Jerome for reviewing.
          To Reproduce the bug, set system timezone to server local time:
          System administration->Location->Location Settings->Default timezone (use Server's local time)

          Go to user profile, change timezone to Server's local time

          Then go to log report page, use this link (change domain to yours):

          https://moodle.yourdomain.local/course/report/log/index.php

          Change 'Display on page' option to 'Download in Excel format', then you will be see the log report in excel format.

          Show
          Dongsheng Cai added a comment - - edited Thanks Jerome for reviewing. To Reproduce the bug, set system timezone to server local time: System administration->Location->Location Settings->Default timezone (use Server's local time) Go to user profile, change timezone to Server's local time Then go to log report page, use this link (change domain to yours): https://moodle.yourdomain.local/course/report/log/index.php Change 'Display on page' option to 'Download in Excel format', then you will be see the log report in excel format.
          Hide
          Jérôme Mouneyrac added a comment - - edited

          peer reviewed on 1.9 and 2.0: fixed

          Show
          Jérôme Mouneyrac added a comment - - edited peer reviewed on 1.9 and 2.0: fixed
          Hide
          Petr Škoda added a comment -

          closing, thanks

          Show
          Petr Škoda added a comment - closing, thanks
          Hide
          hardikraval added a comment - - edited

          Hi,

          I've found a problem on excel generate logs are displaying wrong time compare to actual log time..it's only in excel format.. actually it's also provide wrong date but after putting following code..date comes perfect but time till comes 2 hour behind then real...Ex. Real Time 2:30 pm and Excel shows 12:30 pm

          I have put this code inside..course/lib.php

          $excelTime=25569+$log->time/(3600*24)

          $myxls->write($row, 1, $excelTime+$add , $formatDate);

          And also tried this code which is given in this link

          https://tracker.moodle.org/browse/MDL-27577

          in root/lib/excellib.class.php but nothig happen..

          can any one show me how can i fixed this or add 2 hour more inside this format.

          Please any one help me out!!

          Thanks In Advance!!

          Show
          hardikraval added a comment - - edited Hi, I've found a problem on excel generate logs are displaying wrong time compare to actual log time..it's only in excel format.. actually it's also provide wrong date but after putting following code..date comes perfect but time till comes 2 hour behind then real...Ex. Real Time 2:30 pm and Excel shows 12:30 pm I have put this code inside..course/lib.php $excelTime=25569+$log->time/(3600*24) $myxls->write($row, 1, $excelTime+$add , $formatDate); And also tried this code which is given in this link https://tracker.moodle.org/browse/MDL-27577 in root/lib/excellib.class.php but nothig happen.. can any one show me how can i fixed this or add 2 hour more inside this format. Please any one help me out!! Thanks In Advance!!

            People

            • Votes:
              4 Vote for this issue
              Watchers:
              15 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: