Latest revision as of 17:43, 11 July 2020

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.

@@ Line 193: / Line 193: @@
 === Updating ===
-This is an appropriate way to do so (example ctime field of activity_queue table):
+This is a way to do so (example ctime field of activity_queue table):
 <pre>
@@ Line 217: / Line 217: @@
        }
 </pre>
+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 ====

Proposals/timezones: Difference between revisions

From Mahara Wiki

< Proposals