Allow display datetime time_zone to be different from time_zone of the data saved in database

PHPMaker Feature Requests (PHPMaker)
1

At this time, the time_zone setting sets the datetime display as well as the data saved in the database.

It would be beneficial to separate the setting for the time database timezone (for example, UTC in most cases ) from the display time zone (for example, America/New_York ).

This design keeps the data itself compatible and portable to any timezone as long as the application sets the desired display timezone.

2

The locale time_zone setting will not be used to set the database time zone.

To set database time zone, you may set the advanced setting Database time zone (for SET TIME ZONE).

3

We have tried that…setting the DATABASE TIME ZONE TO UTC and the locale time zone to America/New York. The date time data gets saved as New York.

We raised this with support… You were asked to put in a feature request.

5

If you use DATETIME columns in MySQL, according to MySQL docs:

The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns.

If you can alter table, you may use TIMESTAMP instead. MySQL docs says:

The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

Better still, you may convert all date time stored in database to UTC, if possible. It is considered best practice to store datetime values in UTC and avoid time zone issues, e.g.

UPDATE MyTable
SET MyDateTime = CONVERT_TZ(MyDateTime, 'America/New_York', 'UTC');