Moodle timezone set but on-demand reports show as not run

[mikehenry1979-bah]

Moodle config.php has date_default_timezone_set('America/New_York'); and Site Administration/Location/Location Settings shows default timezone as the same, but on-demand reports show as "this query has not yet been run". Adding date_default_timezone_set('America/New_York'); to line 101 of locallib.php fixes this issue. Our system encountered this issue with version 4.0 of this plugin and it is still present in 4.1. I thought about involvement of the setting "allow users to select their own timezone" but the profile of the user attempting the report shows "server timezone (America/New_York)". Is there also some PHP-level setting?

[timhunt]

Thank you for the bug report.

We don't see on our system (which is Europe/London).

I cannot immediately see why adding that line of code would fix the problem. I would be better to understand it more before changing things, so any extra information you can give would be helpful.

[U-R-dev]

We have encountered the same situation. In "Reports" > "Ad-hoc database queries" page, On-demand queries show information on the last runs, such as:

"This query was last run on Friday, 29 December 2023, 7:48 PM. It took 0.017s to run. Available to ...", with correct timestamps in local timezone Asia/Tokyo.

However, clicking any On-demand queries returns "This query has not yet been run." At the same time, under /moodledata/admin_report_customsql/temp/X directory, new csv file with the UTC timestamp is created.

Our environment is as follows:

• OS: Ubuntu 22.04.3 LTS
• Moodle Version: 4.3.2
• Ad-hoc database queries Version: 4.2 for Moodle 3.9+ (2022031800)
• PHP: 8.0.27
• DB: Postgres 15.1
• Server Timezone: Asia/Tokyo (JST, +0900)
• date.timezone in phpinfo(): Asia/Tokyo
• moodle's timezone setting: "echo $CFG->timezone" shows "Asia/Tokyo"

As suggested by @mikehenry1979-bah, putting date_default_timezone_set('Asia/Tokyo'); into the top of report_customsql_generate_csv funtion in locallib.php resolves the issue; query result is correctly shown in the query result page, and new csv file with its name including local timezone timestamp created.

Some further investigation revealed that, in view.php file, date_default_timezone_get returns "Asia/Tokyo" at first, but "Europe/London" is returned just after admin_externalpage_setup function is called at the line 43, until echo $OUTPUT->header(); is stated at the line 145. Since report_customsql_generate_csv function is called at the line 124, I assume it creates csv file with filename based on "Europe/London" timezone.

I could not figure out why admin_externalpage_setup function changes date_default_timezone variable, but putting date_default_timezone_set($CFG->timezone); just after admin_externalpage_setup also resolved the issue.

Hope this helps in further investigation.

[timhunt]

So, if I understand your analysis correctly, something in admin_externalpage_setup is setting the timezone to "Europe/London". It would be really good to work out what is doing that, since it seems like a bug to me.

[institut-agro-ead]

Hi,
same issue here.
My take on this is that before $OUTPUT->header() is called, the default server timezone is used, meaning the CSV file is created using the server timezone. But when trying to read the file (after $OUTPUT->header()), the name generated by report_customsql_csv_filename uses the timestamp set in Moodle $CFG object.
The best option would be to use the Moodle $CFG at all times to be consistent.
In locallib.php, report_customsql_temp_cvs_name and report_customsql_scheduled_cvs_name both use the PHP strftime (which by the way is deprecated since 8.1) for which you can’t specify which timezone the given timestamp should use.

To fix that, you have to create a new DateTime obect and set the timestamp and timezone manually before formatting: new DateTime())->setTimestamp($timestamp)->setTimezone(new DateTimeZone($CFG->timezone))->format('Ymd-His').
To avoid repetition in both functions, you could create a specific function for that:

function report_customsql_temp_cvs_name($reportid, $timestamp) {
    global $CFG;
    $path = 'admin_report_customsql/temp/'.$reportid;
    make_upload_directory($path);
    return array($CFG->dataroot.'/'.$path.'/'. report_customsql_formatdatetime_from_timestamp($timestamp).'.csv',
                 $timestamp);
}

function report_customsql_scheduled_cvs_name($reportid, $timestart) {
    global $CFG;
    $path = 'admin_report_customsql/'.$reportid;
    make_upload_directory($path);
    return array($CFG->dataroot.'/'.$path.'/'.report_customsql_formatdatetime_from_timestamp($timestart).'.csv',
                 $timestart);
}

function report_customsql_formatdatetime_from_timestamp($timestamp) {
    global $CFG;
    return (new DateTime())->setTimestamp($timestamp)->setTimezone(new DateTimeZone($CFG->timezone))->format('Ymd-His');
}