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

      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?

        Gliffy Diagrams

        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 Skoda added a comment -

            closing, thanks

            Show
            Petr Skoda 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: