Introduction

CONVERT_TZ() returns NULL in MySQL when the timezone tables are not populated. MySQL needs timezone data loaded into the mysql.time_zone* system tables to convert between named timezones like 'America/New_York'. By default, these tables are empty after installation. The fix is to run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql on Linux, or download timezone data from MySQL's website for Windows. After loading, CONVERT_TZ('2025-01-01 12:00:00', 'UTC', 'America/New_York') works correctly.

The Problem

1-- Returns NULL instead of the converted datetime
2SELECT CONVERT_TZ('2025-01-15 12:00:00', 'UTC', 'America/New_York');
3-- Result: NULL
4
5-- Named timezones don't work
6SELECT CONVERT_TZ(NOW(), 'US/Eastern', 'US/Pacific');
7-- Result: NULL
8
9-- But offset-based conversion DOES work
10SELECT CONVERT_TZ('2025-01-15 12:00:00', '+00:00', '-05:00');
11-- Result: 2025-01-15 07:00:00

If CONVERT_TZ returns NULL with named timezones but works with offset notation (+00:00), the timezone tables are empty.

Diagnosing the Issue

1-- Check if timezone tables are populated
2SELECT COUNT(*) FROM mysql.time_zone;
3-- If 0, timezone data is not loaded
4
5SELECT COUNT(*) FROM mysql.time_zone_name;
6-- If 0, named timezones are not available
7
8-- Check for a specific timezone
9SELECT * FROM mysql.time_zone_name WHERE Name = 'America/New_York';
10-- Empty result = timezone not loaded

MySQL stores timezone data in five system tables: time_zone, time_zone_name, time_zone_transition, time_zone_transition_type, and time_zone_leap_second. All must be populated for named timezone conversion to work.

Fix: Load Timezone Data (Linux/macOS)

1# Load timezone data from the OS into MySQL
2mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
3
4# If you get warnings about leap seconds, ignore them:
5mysql_tzinfo_to_sql /usr/share/zoneinfo 2>/dev/null | mysql -u root -p mysql
6
7# Verify it worked
8mysql -u root -p -e "SELECT COUNT(*) FROM mysql.time_zone_name;"
9# Should return 500+ rows

1-- After loading, CONVERT_TZ works with named timezones
2SELECT CONVERT_TZ('2025-01-15 12:00:00', 'UTC', 'America/New_York');
3-- Result: 2025-01-15 07:00:00
4
5SELECT CONVERT_TZ('2025-07-15 12:00:00', 'UTC', 'America/New_York');
6-- Result: 2025-07-15 08:00:00 (DST applied automatically)

The mysql_tzinfo_to_sql utility reads the OS timezone files and generates SQL statements to populate MySQL's timezone tables.

Fix: Load Timezone Data (Windows)

1# Windows does not have /usr/share/zoneinfo
2# Download timezone data from MySQL:
3# https://dev.mysql.com/downloads/timezones.html
4
5# Import the downloaded SQL file
6mysql -u root -p mysql < timezone_posix.sql

Windows does not ship POSIX timezone files. Download the prebuilt SQL file from MySQL's website and import it manually.

Fix: Docker Containers

1# Dockerfile — load timezone data during image build
2FROM mysql:8.0
3
4# Copy timezone loading script
5RUN mysql_tzinfo_to_sql /usr/share/zoneinfo > /docker-entrypoint-initdb.d/timezones.sql

1# docker-compose.yml — load timezone data on first run
2services:
3  db:
4    image: mysql:8.0
5    volumes:
6      - ./init-scripts:/docker-entrypoint-initdb.d
7    environment:
8      MYSQL_ROOT_PASSWORD: secret

# init-scripts/01-timezones.sh
#!/bin/bash
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p"$MYSQL_ROOT_PASSWORD" mysql

Docker MySQL containers start with empty timezone tables. Add an init script to load them on first startup.

Workaround: Use UTC Offsets

1-- If you can't load timezone data, use UTC offsets
2SELECT CONVERT_TZ('2025-01-15 12:00:00', '+00:00', '-05:00');
3-- Result: 2025-01-15 07:00:00
4
5-- Common offsets:
6-- UTC:              +00:00
7-- US Eastern (EST): -05:00
8-- US Eastern (EDT): -04:00
9-- US Pacific (PST): -08:00
10-- US Pacific (PDT): -07:00
11-- India (IST):      +05:30
12-- Japan (JST):      +09:00

UTC offsets always work regardless of timezone table state. However, they do not handle daylight saving time transitions — you must manually use the correct offset for each date.

Using CONVERT_TZ Correctly

1-- Convert stored UTC to user's local time
2SELECT
3    id,
4    event_name,
5    CONVERT_TZ(event_time, 'UTC', 'America/Chicago') AS local_time
6FROM events;
7
8-- Convert user input to UTC for storage
9INSERT INTO events (event_name, event_time)
10VALUES ('Meeting', CONVERT_TZ('2025-03-15 14:00:00', 'America/Chicago', 'UTC'));
11
12-- Handle NULL results gracefully
13SELECT COALESCE(
14    CONVERT_TZ(created_at, 'UTC', 'America/New_York'),
15    created_at  -- Fallback to original if conversion fails
16) AS display_time
17FROM orders;

Updating Timezone Data

1# After OS timezone updates (e.g., a country changes its DST rules)
2# Re-run the timezone loader
3mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
4
5# Update the OS timezone data first
6# Ubuntu/Debian:
7sudo apt update && sudo apt install tzdata
8
9# CentOS/RHEL:
10sudo yum update tzdata
11
12# macOS:
13# Timezone data updates come with macOS system updates

Timezone rules change periodically (countries modify DST dates). After updating the OS timezone data, re-run mysql_tzinfo_to_sql to keep MySQL in sync.

Common Pitfalls

• Assuming timezone tables are populated by default: MySQL installation does NOT load timezone data automatically. The mysql.time_zone* tables are empty until you explicitly run mysql_tzinfo_to_sql. This catches nearly every new MySQL setup.
• Using offsets instead of named timezones for DST-aware conversion: CONVERT_TZ(dt, '+00:00', '-05:00') does not handle daylight saving time. America/New_York is UTC-5 in winter and UTC-4 in summer. Only named timezones handle DST transitions automatically.
• Not updating timezone data after OS updates: When a country changes its DST rules, the OS timezone files are updated via package managers, but MySQL's tables remain stale. Re-run mysql_tzinfo_to_sql after tzdata updates.
• Missing permissions to write to mysql database: Loading timezone data requires write access to the mysql system database. Regular users typically cannot run the load command — use the root MySQL user or a user with INSERT privileges on mysql.*.
• Docker containers losing timezone data on restart: If MySQL data is not persisted via a volume, timezone data loaded during runtime is lost when the container restarts. Use an init script in /docker-entrypoint-initdb.d/ or a custom Dockerfile to load timezone data during image build.

Summary

• CONVERT_TZ() returns NULL when MySQL's timezone tables are empty (the default after installation)
• Run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql to load timezone data on Linux/macOS
• On Windows, download the timezone SQL file from MySQL's website and import it
• UTC offset notation (+00:00, -05:00) always works but does not handle daylight saving time
• Named timezones (America/New_York) automatically handle DST transitions
• Re-run the timezone loader after OS tzdata updates to keep MySQL's data current