Times reported inaccurately when DB timezone does not match PHP timezone

[Mark Webster]

See https://wiki.mahara.org/index.php/System_Administrator%27s_Guide/Installing_Mahara/Troubleshooting#Q:_Time_zone_problems_.2F_Mahara_is_showing_incorrect_times_for_forum_posts for a full breakdown of the problem.

TL,DR Mahara has no support for multiple timezones and assumes that the PHP date.timezone and the database server's timezone are identical. If they are not, then all dates that get stored into the database and displayed back to the user will be off by (Database UTC offset - PHP UTC offset).

[Kristina Hoeppner (kris-hoeppner)]

Thanks for going to look into this, Mark.

This will need to be tested with both Postgres and MySQL.

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/8866

[Mark Webster (mark-webster-v)]

The submitted patch mitigates the problem as much as possible, but has some limitations due to the way Mahra stores dates/times in the DB.

A full fix will require changing Mahara to store dates/times as a unix epoch value.

[Steven (stevens-q)]

Preconditions:
-----------------------------
1. Admin sets site settings to country = Australia
2. Admin sets the
settings to Timezone = No Timezone Selected

Test Script:
-----------------------------
1. Log in as admin user
2. Create a new user (User99)
3. Log in as User99
4. Browse to Main menu > Content > Profile > Contact information
5. Confirm that the "Country" field has auto selected the same country as Site settings (Australia) ✔
6. User create a new portfolio page (Timezone Test)
7. Note the date created (date/time) when viewing the displayed page (ie. Updated on 01 June 2018, 9:32 AM)
8. Log out and back in as admin user
9. Browse to Admin menu > Configure site > Site options > Site Settings
10. Confirm that the "Contry" field has remained the same as original setting (Australia) ✔
11. Confirm that the "Timezone" field has auto selected to Antarctica/Macquaire (This is expected behaviour) ✔

NOTE: Antarctica/Macquaire is (regionally part of Oceania and politically a part of Tasmania, Australia, since 1900) and is alphabetically first in the list of Australia time zones

12. Select "Australia/Perth" from the Timezone dropdown field and save site settings
13. Admin
create a new portfolio page (Timezone Test2)
14. Confirm
the date created (date/time) when viewing the displayed page is Approximately 3 hours difference (Updated on 01 June 2018, 6:49 AM) ✔

NOTE: The Expected behaviour - If a country with multiple time zones is selected and timezone field is set to No Timezone Selected - when content is created the timezone used the first alphabetical timezone for that country

Catalyst QA Approved ✔

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/8866
Committed: https://git.mahara.org/mahara/mahara/commit/bc25beec2cf7d0d7c1d8a5575ddfe27a5e4e5f05
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit bc25beec2cf7d0d7c1d8a5575ddfe27a5e4e5f05
Author: Mark Webster <email address hidden>
Date: Thu May 17 11:25:16 2018 +0100

Bug 1771362: Fix timezone issues.

Adds timezone dropdown the site settings. If no timezone selected
it uses site settings country to determine correct timezone and sets
that in the database.
PHP and DB session timezones will then assume this setting for all times.

This makes setting $cfg->dbtimezone obsolete - there is a warning to
admins to update their timezone setting on admin dashboard.

Limitations:
- A timezone or country MUST be selected in site options.

- Will not correct existing incorrect times, as no timezone info was
stored in the DB for dates/times, so it's impossible to determine what timezone
was used when saving.

- Changing the timezone will not adjust the display of existing times, for the
same reason.

behatnotneeded

Change-Id: I34c64cd51ece6758e76dd8c37007dcb316005845