Proposals/timezones
From Mahara Wiki
< Proposals
Timezone support for Mahara
THIS IS A DRAFT
Preamble
This change is required to be able to show times to users that are appropriate to their timezones, rather than the timezone the server is in. Currently this is not possible as mahara is storing the time data in a timezone unaware format.
Use cases
This will be relevant for at least two use cases:
Users in timezones different to the server
There are numerous cases where times are shown to users in regards to when posts were made, messages were sent, etc. These can be way off for someone in a timezone different to the server.
If the server moves
At some point, a mahara server may well move. Admins may wish to change the times of the server itself, or within mahara. If the time data is stored without timezone awareness, then this would upset the data.
Implementation
Settings
Users
An option will be made available to users in the general account section of their settings page, ideally immediately above or below the language setting.
There will be no user timezone setting by default.
Institutions
An option will be made available on the institution creation page and in the institution settings page.
There will be no institution timezone setting by default.
Which institution's settings of several will the user respect?
The first institution that a user is added to where the institution has a timezone set will become the user's default timezone, and the user will be notified with instructions to change it if this was not desired. Subsequent institution additions where the institution has a timezone set will notify the user that it is a differing timezone, and offer instructions to the user on how to adopt that timezone.
Server admins
Server admins will be able to set the server default timezone in the Site options page in the site settings fieldset.
The default value for that timezone will be "Server timezone" which is what is reported by PHP at install or upgrade time.
Setting priority
The priority of the timezone settings is:
- User timezone setting has highest priority.
- If user timezone setting is not set, the site-wide timezone setting will be used.
Schema/Code
MySQL timestamps have been stored as 'datetime'. PostgreSQL timestamps have been stored as 'timestamp no time zone'.
Neither of these datatypes retain timezone perspective. They ought to have been recorded as 'timestamp' and 'timestamp with time zone' respectively.
From the Moodle XMLDB docs:
(*) Note that, although datetime types are supported by XMLDB, all datetime/timestamp columns under Moodle are declared as integer(10) and filled with Unix timestamps, so this type of column shouldn't be used at all (in fact, the XMLDB Editor doesn't show them as an available option).
The datetime/timestamp fields in the database ought to be converted to integer(10) so as to take the unix timestamp.
Applying the timezone
The timezone name (e.g. "Pacific/Auckland") for the user will be loaded into the user object at the point the user logs in or changes the setting.
The current offset for that timezone will be applied to the time data pulled from the database before the timestamp is converted to human-readable format.
Technical Details
The following fields need converting
activity_queue ctime artefact ctime mtime atime artefact_cron nextrun artefact_internal_profile_email expiry artefact_log "time" artefact_plans_task completiondate artefact_resume_personal_information dateofbirth auth_cron nextrun blocktype_cron nextrun blocktype_externalfeed_data lastupdate blocktype_wall_post postdate collection ctime mtime cron nextrun export_cron nextrun favorite ctime mtime "group" ctime mtime group_member ctime group_member_invite ctime group_member_request ctime grouptype_cron nextrun import_cron nextrun import_queue expirytime institution expiry interaction_cron nextrun interaction_forum_edit ctime interaction_forum_post ctime interaction_instance ctime notification_cron nextrun notification_emaildigest_queue ctime notification_internal_activity ctime search_cron nextrun site_content ctime mtime site_data ctime usr expiry lastlogin lastlastlogin lastaccess suspendedctime ctime usr_friend ctime usr_friend_request ctime usr_infectedupload "time" usr_institution ctime expiry usr_institution_request ctime usr_password_request expiry usr_registration expiry usr_session ctime usr_watchlist_view ctime view startdate stopdate ctime mtime atime submittedtime view_access startdate stopdate ctime
Schema
The install.xml file will need to be editted in respect to the above list of fields.
Updating
This is a way to do so (example ctime field of activity_queue table):
$tabledata = get_records_array('activity_queue', '', '', '', 'id,ctime');
$table = new XMLDBTable('activity_queue');
$field = new XMLDBField('ctime');
drop_field($table, $field);
$field->setAttributes(XMLDB_TYPE_INTEGER, 10, XMLDB_UNSIGNED, null);
add_field($table, $field);
if ( is_array($tabledata) ) {
foreach ($tabledata as $tdata) {
$new = new stdClass;
$new->ctime = strtotime($tdata->ctime);
$where = new stdClass;
$where->id = $tdata->id;
update_record('activity_queue', $new, $where );
}
}
but it will not work on large sites, so we cannot use this approach.
Instead, we need to find a way to do the conversion in SQL (both on Postgres and MySQL) as much as possible. If we don't, many people will not be able to upgrade.
Code changes
Check all the places in the code where the date is displayed in human readable format.
The following template files have human-readable time records.
./htdocs/interaction/forum/theme/raw/editpost.tpl ./htdocs/artefact/blog/theme/raw/atom.xml.tpl ./htdocs/artefact/blog/theme/raw/posts.tpl ./htdocs/artefact/file/blocktype/filedownload/theme/raw/filedownload.tpl ./htdocs/artefact/file/export/html/theme/raw/index.tpl ./htdocs/artefact/file/theme/raw/form/filelist.tpl ./htdocs/blocktype/groupviews/theme/raw/groupviews.tpl ./htdocs/blocktype/newviews/theme/raw/newviews.tpl ./htdocs/export/html/theme/raw/footer.tpl ./htdocs/export/leap/theme/raw/header.tpl ./htdocs/interaction/forum/theme/raw/atom.xml.tpl ./htdocs/interaction/forum/theme/raw/edittopic.tpl ./htdocs/interaction/forum/theme/raw/simplepost.tpl ./htdocs/interaction/forum/theme/raw/topics.tpl ./htdocs/theme/raw/templates/form/viewacl.tpl ./htdocs/theme/raw/templates/group/info.tpl ./htdocs/theme/raw/templates/group/membersearchresults.tpl ./htdocs/theme/raw/templates/group/topicrows.tpl ./htdocs/theme/raw/templates/performancefooter.tpl ./htdocs/theme/raw/templates/portfoliosearchresults.tpl ./htdocs/theme/raw/templates/user/sendmessage.tpl ./htdocs/theme/raw/templates/view/accesslistrow.tpl ./htdocs/theme/raw/templates/view/sharedviewrows.tpl ./htdocs/theme/raw/templates/view/view.tpl
There will be other places where the code needs changing as it will be coming from the database in a different format to what it currently is.