Native DATETIME WITH TIME ZONE Support in MySQL

[ScottStroz]

Originally submitted by Øystein Grøvlen of Oracle

Abstract:

This proposal introduces a native DATETIME WITH TIME ZONE data type for MySQL to provide standard-compliant temporal handling beyond the limitations of the current TIMESTAMP implementation. The work also includes a broader refactoring of MySQL temporal type internals to improve consistency, type safety, and future extensibility.

Short Description of the Feature:

Implement a native DATETIME WITH TIME ZONE temporal type that stores UTC values together with associated time-zone displacement information, while refactoring MySQL’s internal handling of DATE, TIME, DATETIME, and TIMESTAMP types to improve correctness, maintainability, and support for future temporal enhancements.

Bug DB Link

https://bugs.mysql.com/bug.php?id=120454

Full Description

Implement DATETIME WITH TIME ZONE as a native temporal data type,
together with the required refactoring of MySQL’s temporal type
implementation.

MySQL’s existing TIMESTAMP type has a limited valid range and cannot
represent values beyond 2038. DATETIME has a wider range, but it does
not carry time-zone information. DATETIME WITH TIME ZONE would provide
a standard-style temporal type that combines the wider DATETIME range
with explicit time-zone displacement information.

The type should store a UTC datetime component together with the
associated time-zone displacement. Comparisons, indexing, and
calculations should be based on the UTC value, while values returned
to users should preserve and show the associated displacement. This
makes it possible to represent the same instant consistently while
still retaining the local time context supplied by the user or
application.

This work also includes cleanup of the internal implementation of
temporal types. The current implementation relies heavily on generic
temporal representations, which makes the code harder to reason about
and can lead to inconsistencies. Refactoring the handling of TIME,
DATE, DATETIME, and TIMESTAMP into more dedicated internal
representations should improve type safety, simplify Field and Item
interfaces, reduce conversion mistakes, and keep common operations
such as storage, retrieval, movement, and comparison efficient.

The goal is to provide a better foundation for future replacement of
TIMESTAMP use cases, including those affected by the 2038 limit, and
for improving temporal handling in areas where the server currently
mixes local-time and UTC-based behavior.

Proposal

TemporalDataTypes.pdf