[RFC|Merged] Switch to PHP timezone handling

[ToonArmy]

PR
https://github.com/phpbb/phpbb3/pull/843
Ticket
http://tracker.phpbb.com/browse/PHPBB3-9558

Introduction
Currently we store UTC UNIX timestamps of every entity which has an attached date and time, and convert these to the users timezone and localise them when the page is rendered using user::date_format(). This poses two notable shortcomings:

• Users and administrator has to manually adjust the timezone when DST changes
• Timezone information on the entity is lost

Proposal
Tackling of this problem basically falls into two distinct sets of changes, first the user timezone handling should use the PHP DateTime(Zone) classes. Secondly entity columns should be modified to include the timezone so that differences can be calculated correctly.

User facing

• Firstly all the phpBB date formatting code should be ported to work inside the DateTime::format() method, thus all phpBB DateTime's will be formatted correctly by default.
• The existing user_timezone and user_dst columns will be dropped and replaced with a varchar user_timezone column containing the textual timezone identifiers. Existing settings will be converted to the Etc/±X timezones.
• The board_timezone and board_dst configuration options will be deleted and the user prompted to enter a new default timezone on upgrade - stored in board_timezone.
• user::format_date() will continue to act as a legacy wrapper to format UTC UNIX timestamps using the new DateTime class.
• A method will be added to the user class to instantiate a DateTime object in the users timezone and locale.

Date time storage
This will probably be deferred to a second RFC for 3.2 but basically existing integer types for UNIX timestamps on various tables will be adjusted to either a database specific timezoneless datetime data type or a text column for an ISO-8601, and an additional column for the timezone identifier will be added. Existing times will be stored in the new columns as if there were created at UTC.

First patch: https://github.com/p/phpbb3/compare/dev ... z-handling

[ToonArmy]

Changes so far for this are displayed here: http://github.com/cs278/phpbb3/compare/ ... z-handling (note I'll be rebasing the branch with a ticket ID for the commit messages in the coming days so beware!)

[bantu]

I agree with deferring "Date time storage" to 3.2.

[Oleg]

This is my #1 wanted feature in phpbb.

Secondly entity columns should be modified to include the timezone so that differences can be calculated correctly.

This will probably be deferred to a second RFC for 3.2 but basically existing integer types for UNIX timestamps on various tables will be adjusted to either a database specific timezoneless datetime data type or a text column for an ISO-8601, and an additional column for the timezone identifier will be added.

I'd like to see more justification for this part. If dates are stored as dates in the database, what happens is each such date must be parsed in the app code. Python/ruby folks by now realize that this parsing takes time, and various orm implementations have faq entries and such explaining how to bypass it when performance is (very) important.

Unlike python and ruby that use orms phpbb keeps data in dumb arrays, so either 1) database layer must return dates as date objects (extremely unlikely?) or 2) instead of getting integers for timestamps code would be getting strings for dates, which are basically unusable directly. When are dates going to be parsed, who will be responsible for parsing them, where will parsed date objects be stored?

I suppose dbal may parse date strings into date objects, but this won't be free and may nontrivially affect people on shared hosting who have cpu resource limits.

On the diff:

phpbb_datetime is used in a different case at the very end of the diff: phpbb_DateTime.

[ToonArmy]

Secondly entity columns should be modified to include the timezone so that differences can be calculated correctly.

This will probably be deferred to a second RFC for 3.2 but basically existing integer types for UNIX timestamps on various tables will be adjusted to either a database specific timezoneless datetime data type or a text column for an ISO-8601, and an additional column for the timezone identifier will be added.

I'd like to see more justification for this part. If dates are stored as dates in the database, what happens is each such date must be parsed in the app code. Python/ruby folks by now realize that this parsing takes time, and various orm implementations have faq entries and such explaining how to bypass it when performance is (very) important.

Unlike python and ruby that use orms phpbb keeps data in dumb arrays, so either 1) database layer must return dates as date objects (extremely unlikely?) or 2) instead of getting integers for timestamps code would be getting strings for dates, which are basically unusable directly. When are dates going to be parsed, who will be responsible for parsing them, where will parsed date objects be stored?

Well as I'm fairly certain I don't want to touch this before 3.2 can we leave the discussion until a later date? It is entirely possible just to use a UNIX timestamp and store the timezone that it is in though.

phpbb_datetime is used in a different case at the very end of the diff: phpbb_DateTime.

Fixed

[Oleg]

It is entirely possible just to use a UNIX timestamp and store the timezone that it is in though.

I actually don't see the purpose of storing the timezone at all. Said differently I don't understand what knowing the timezone in which something was sent or posted gives us.

[naderman]

That only really makes sense if you store the timestamp in that timezone. So that you can later correctly convert from that timezone to the viewing user's. http://derickrethans.nl/storing-date-ti ... abase.html is worth reading in this context. Since we want to be able to sort by dates, it's preferable that all datetimes are stored in the same timezone (we use UTC at the moment). The problem is that such conversion is inaccurate for past dates as explained in the blog post I linked to. But I agree that at least for now fixing DST and timezone selection in the proposed way should be enough. We can think about the actual storage of dates again later (or at least in a separate topic).

I entirely agree with the actual subject of the RFC and the proposed solution. Please also create a ticket on the tracker.

[Oleg]

Friendly bump to get this feature in before the feature freeze.

Is any help needed?

Tracker ticket had been created: http://tracker.phpbb.com/browse/PHPBB3-9558

[ToonArmy]

I've done more work on this today, http://github.com/cs278/phpbb3/compare/ ... z-handling

[ToonArmy]

I've done more work on this today, http://github.com/cs278/phpbb3/compare/ ... z-handling

Further changes altering the schema and any files that do date calculations have been pushed.