Which temporal data type is best for multi-regional time zone synchronization?

Handling Regional Time Zones in MySQL

A common architectural challenge in MySQL involves choosing between TIMESTAMP and DATETIME when the application must support users across multiple regional time zones. The goal is to ensure that a single event is recorded once but displayed accurately according to the local time of each viewing user.

Constraints and Behavior

• TIMESTAMP automatically converts values to UTC for storage and back to the current session time zone for retrieval.
• DATETIME stores the literal value provided, remaining constant regardless of session or global time zone settings.
• The CONVERT_TZ() function provides a mechanism for explicit transformation, provided the system time zone tables are populated.

Given the 2038 limit of TIMESTAMP and the static nature of DATETIME, there is uncertainty regarding which approach minimizes conversion overhead while maintaining data integrity for long-term historical records.

Which data type provides the most reliable foundation for a global application? Does relying on TIMESTAMP's implicit conversion introduce too much risk compared to storing DATETIME in UTC and converting manually at the application layer?