[Proposal]: handle the corner cases and unfavourable mix of date and time datatypes

[auge8472]

Alex once began to implement timezone support into MLF. But in the end it was never fully implemented.

This is the reason we have an additional setting in the forum settings (time_difference) as well in the personal settings in the userdata for a numeric time difference to the server time (mlf2_userdata.time_difference). I would like to get rid of both. Beside of this, the personal setting is a smallint (small integer with a value of -32768 to 32767 (signed) or 65535 (unsigned)) and does therefore wether support differences like 4:30 (+4.5, Afghanistan) or +5:45 (+5.75, Nepal) nor [not support] changes to and from summer time/daylight saving time.

[edit]
The settings does support such time differences but the forum operator and the users have to compute their time difference. See therefore the third comment below.
[/edit]

The forum operator for the forum and a user for herself/himself can alternatively specify a home timezone. Because of the currently not satisfactorily existing support of timezones in MySQL and MariaDB (there seems to be movement for the latter) it is better to do the timezone handling in PHP. MySQL can reliable only work with numeric time differences (without support for summer time/daylight saving time), timezone names are only supported in systems, that has a timezone table. Unixoids have such a table, MS Windows only in cases, the system administrator imported one.

The prerequisite for reliably converting time values in PHP is to store the datetime values either completely in the home timezone of the server or completely in UTC.

Alex decided to store all datetime values in columns of the type timestamp. This type stores the values internally as (UTC-based) unix timestamps but output the data in the ISO datetime format (as example: 2024-04-22 13:58:20) without any further precautions. The medium term pitfall: The datatype has (afaik) the same restrictions like the unix timestamp, its size is 32 Bit and it can handle only values between 01.01.1970 00:00:00 and 2038-01-19 03:14:07. I don't know if MySQL or MariaDB does or will support 64-Bit-unix-timestamps by itself.

The alternative is the datatype datetime with a much wider range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. But this datatype stores the values in the server time and not in UTC. It is possible to convert a time value to UTC using on-board tools.

CAST(time AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS utc_time

[edit]:
Last but not least, the MySQL documentation does not provide the information about the version, a functionallity was introduced. The documentation for older MySQL versions redirects the visitor to their counterpart pages in the documentations of newer server versions. We can not ensure a function to work with any MySQL version, that we marked as supported due to limited testing possibilities.
[/edit]

Most of the datetime-values in the MLF2-tables get stored in columns of the type timestamp but for newer tables we also use datetime in some cases. As far as I can see, these columns were all introduced by me.

• mlf2_subscriptions.tstamp
• mlf2_uploads.tstamp
• mlf2_userdata.dps_accepted
• mlf2_userdata.tou_accepted

I propose to either change the datetime columns to the type timestamp even though the associated year-2038-problem is known or to change all datetime columns to the type datetime and to handle their values strictly with the conversion to UTC. Both in combination with further conversions on the PHP side.

However, I don't know if my thoughts are going in an overcomplicated direction. Thatswhy and because this is no urgent issue I would like to put my ideas up for discussion.

[auge8472]

Additional information from the MySQL 8.0 documentation (function CONVERT_TZ):

On 64-bit platforms, beginning with MySQL 8.0.28, the maximum supported value is '3001-01-18 23:59:59.999999' UTC.

[auge8472]

The oldest available manual for the MySQL-version (5.7) knows the function CONVERT_TZ.

[auge8472]

[the personal and also forum wide settings] does [not] support differences like 4:30 (+4.5, Afghanistan) or +5:45 (+5.75, Nepal)

I stand corrected (from the configuration page in the wiki):

time_difference: difference between server and forum time in minutes

The settings does support such time differences but the forum operator and the users have to compute their time difference.

[loesler]

I understand your point, but I have no preference except that the datetime-type should be consistent.