Timestamps and Time Zones in MySQL

SET time_zone = 'America/New_York';

CREATE TABLE events (
    ts  TIMESTAMP,
    dt  DATETIME
);

INSERT INTO events VALUES ('2024-03-15 12:00:00', '2024-03-15 12:00:00');

-- Switch to a different timezone and re-read
SET time_zone = 'America/Los_Angeles';
SELECT ts, dt FROM events;
-- ts: 2024-03-15 09:00:00  (converted: UTC-8 vs UTC-5 = 3 hours earlier)
-- dt: 2024-03-15 12:00:00  (unchanged)

-- This will fail or behave unexpectedly near or after 2038
expiry_date TIMESTAMP   -- wrong

-- Use DATETIME for dates beyond 2038
expiry_date DATETIME    -- correct

-- Server's global time zone (set at startup or in my.cnf)
SELECT @@global.time_zone;

-- Session time zone (can differ per connection)
SELECT @@session.time_zone;

# my.cnf
[mysqld]
default-time-zone = '+00:00'

SET GLOBAL time_zone = '+00:00';
SET SESSION time_zone = '+00:00';

-- Check if named timezone tables are loaded
SELECT COUNT(*) FROM mysql.time_zone_name;

-- Load them (run from the OS shell)
-- mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

-- Convert between time zones
SELECT CONVERT_TZ('2024-03-15 12:00:00', 'America/New_York', 'UTC');
-- Returns: 2024-03-15 16:00:00  (March 15 is EDT = UTC-4, so noon EDT = 16:00 UTC)

CREATE TABLE posts (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP