TIMESTAMP vs DATETIME for regional time zone conversion

For a system requiring long‑term archival beyond 2038 and regional audit trails, the DATETIME + Time Zone Identifier approach is the only sustainable choice.

Analysis of Trade‑offs

The 2038 Epoch Limit

In MariaDB and MySQL, TIMESTAMP is stored as a 4‑byte integer representing seconds since the Unix epoch. This limits the range to 1970‑01‑01 through 2038‑01‑19. For any system intended for long‑term retention or auditing, this creates a critical failure point. DATETIME uses a significantly larger range (1000‑01‑01 to 9999‑12‑31), removing this architectural risk.

Session‑Based vs. Explicit Conversion

While TIMESTAMP handles UTC conversion automatically based on the session’s time_zone variable, this creates a dependency on the application’s connection state. If a session is misconfigured or a global server change occurs, the retrieved audit trail may be shifted incorrectly without a trace of why.

Using DATETIME with an explicit time zone column (e.g., event_tz storing 'America/New_York') provides a deterministic audit trail. You store the UTC value in the DATETIME column and the origin zone in the identifier column. This ensures that the "wall-clock time" at the origin is preserved regardless of where the report is generated.

Implementation Steps for Regional Auditing

1. Schema Design: Create a DATETIME column for the UTC event time and a VARCHAR column for the IANA time zone ID (e.g., Europe/London).
2. Ingestion: Convert all incoming regional timestamps to UTC at the application level or using UTC_TIMESTAMP() before inserting into the DATETIME column.
3. Retrieval: Use CONVERT_TZ() to shift the UTC DATETIME back to the stored regional zone for reporting:

SELECT CONVERT_TZ(event_utc, '+00:00', event_tz) AS local_time FROM event_logs;

Verification

To verify the behavior of these types in your current environment, execute the following sequence:

-- Create test table
CREATE TABLE tz_test (ts TIMESTAMP, dt DATETIME);
INSERT INTO tz_test VALUES (NOW(), NOW());

-- Check current session (usually system time)
SELECT * FROM tz_test;

-- Change session to UTC and observe that only 'ts' changes
SET time_zone = '+00:00';
SELECT * FROM tz_test;

Missing Diagnostic Detail

Are you using a MariaDB version that supports the TIMESTAMP range extension, or are you bound to the standard 32‑bit Unix epoch implementation?