Proposals/timezones

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

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.

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 likely appropriate way to do so (example ctime field of activity_queue table):

$tabledata = get_records_array('activity_queue', '', '', '', 'id,ctime'));

db_begin();

$table = new XMLDBTable('activity_queue');
$field = new XMLDBField('ctime');
$field->setAttributes(XMLDB_TYPE_INTEGER, 10, current_timestamp, XMLDB_NOTNULL);
change_field_type($table, $field, true, true);

foreach ($tabledata as $tdata) {
    update_record('activity_queue', (object) array( 'ctime' => strtotime($tdata['ctime']) ), (object) array( 'id' = $tdata['id'] ) );
}

db_commit();

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

Resources

http://stackoverflow.com/questions/2689428/mysql-convert-db-from-local-time-to-utc http://stackoverflow.com/questions/2700197/sql-server-convert-date-field-to-utc