How to Handle Time Zones in MySQL Database

I’ve been bitten by time zone bugs more than once — a “midnight” event that showed up as 8 PM the day before for users on the West Coast, a report that was off by exactly one hour every time daylight saving kicked in. Time zones are one of those topics that seem simple until you’re debugging them at 11 PM. In this article, I’ll share the approach I now use consistently for handling time zones in MySQL, and why I insist on it for every project.

Why Time Zones Are Tricky in Databases

The core problem is that a timestamp without an associated time zone is ambiguous. 2026-08-01 14:00:00 could mean 2 PM in New York, 2 PM in Tokyo, or 2 PM UTC — and these are three completely different instants in time. Add daylight saving time transitions, and things get even messier: some local times don’t exist at all (during “spring forward”), and some occur twice (during “fall back”).

My rule, which I apply almost universally now: store everything in UTC, convert to local time only for display.

MySQL’s Date and Time Data Types

Before discussing time zones, it’s worth being precise about MySQL’s relevant types:

This distinction is the single most important thing to understand: TIMESTAMP is time-zone-aware in behavior (it’s converted); DATETIME is not.

How TIMESTAMP Actually Works Internally

sequenceDiagram
    participant App as Application
    participant MySQL as MySQL Server
    participant Disk as Storage (UTC)

    App->>MySQL: INSERT with session tz = 'America/New_York'
    MySQL->>MySQL: Convert local time to UTC
    MySQL->>Disk: Store UTC value
    App->>MySQL: SELECT with session tz = 'Asia/Tokyo'
    MySQL->>Disk: Read UTC value
    MySQL->>MySQL: Convert UTC to Asia/Tokyo
    MySQL->>App: Return Tokyo local time

When you insert a TIMESTAMP value, MySQL converts it from the current session time zone to UTC for storage. When you read it back, MySQL converts from UTC to whatever the current session time zone is at read time — which might be different from the session that wrote it. This is powerful, but also the source of a lot of confusion if you don’t set your session time zone deliberately.

DATETIME, by contrast, stores exactly the literal value you gave it, with zero conversion, ever.

Checking and Setting the Server Time Zone

SELECT @@global.time_zone, @@session.time_zone;

Typical output:

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM              | SYSTEM              |
+--------------------+---------------------+

SYSTEM means MySQL is using the underlying OS’s time zone setting — which I actively avoid relying on, because it makes behavior dependent on server configuration rather than something explicit in my schema or application.

I explicitly set the global time zone to UTC:

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

Or permanently in the config file:

[mysqld]
default-time-zone='+00:00'

Loading Named Time Zones

Using offsets like +00:00 works, but named zones like America/New_York are more useful because they automatically account for daylight saving transitions. To use named zones, you first need to load the time zone tables:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

I run this once per server after installation. After loading, I can verify:

SELECT * FROM mysql.time_zone_name LIMIT 5;

+---------------------------+-------------+
| Name                      | Time_zone_id|
+---------------------------+-------------+
| Africa/Abidjan             | 1           |
| Africa/Accra                | 2           |
| America/New_York            | 145         |
+---------------------------+-------------+

Now I can set a session to a named zone:

SET SESSION time_zone = 'America/New_York';

My Recommended Pattern: UTC Storage, Application-Layer Conversion

Here’s the schema pattern I use on virtually every project:

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  starts_at_utc TIMESTAMP NOT NULL,
  user_timezone VARCHAR(64) NOT NULL DEFAULT 'UTC'
) ENGINE=InnoDB;

I keep the server and connection time zone fixed at UTC (+00:00), and I store the user’s preferred display time zone as a separate column (user_timezone) rather than trying to make MySQL juggle multiple zones automatically. Conversion to the user’s local time happens in the application layer (or via CONVERT_TZ() at query time, shown below), never by changing session time zone per request.

INSERT INTO events (title, starts_at_utc, user_timezone)
VALUES ('Product Launch', '2026-08-01 18:00:00', 'America/Los_Angeles');

Converting Between Time Zones with CONVERT_TZ()

SELECT title,
  starts_at_utc,
  CONVERT_TZ(starts_at_utc, 'UTC', 'America/Los_Angeles') AS local_time
FROM events;

Output:

+----------------+---------------------+---------------------+
| title          | starts_at_utc       | local_time           |
+----------------+---------------------+---------------------+
| Product Launch | 2026-08-01 18:00:00 | 2026-08-01 11:00:00  |
+----------------+---------------------+---------------------+

I use CONVERT_TZ() extensively for reporting queries where I need to group events by the user’s local calendar day, not UTC day:

SELECT
  DATE(CONVERT_TZ(starts_at_utc, 'UTC', user_timezone)) AS local_date,
  COUNT(*) AS event_count
FROM events
GROUP BY local_date;

Daylight Saving Time: Where Things Get Tricky

A concrete example I ran into: on the day clocks “spring forward” in the US, the local time 2:30 AM doesn’t exist. If code naively constructs that datetime and inserts it as a DATETIME, there’s no error — it just stores a nonsensical value. Using named time zones with CONVERT_TZ() and UTC storage sidesteps this entirely, since UTC has no DST transitions.

-- This is safe because storage is always UTC
SELECT CONVERT_TZ('2026-03-08 02:30:00', 'America/New_York', 'UTC');

MySQL’s named time zone tables handle the DST rules correctly as long as they’re kept up to date (see below).

Keeping Time Zone Data Current

Time zone rules change periodically (countries adjust DST policy, etc.), so I make it a habit to refresh the zoneinfo data and reload it:

# On Linux, update system tzdata package first
sudo apt-get update && sudo apt-get install tzdata

# Then reload into MySQL
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

I schedule this as part of routine server maintenance, roughly twice a year, or whenever I hear of a DST policy change affecting my user base.

Real-World Scenario: Global SaaS Scheduling

For a scheduling application I worked on, users across many countries needed to see meeting times in their own local time, while the backend needed to reliably order and compare events. The pattern:

CREATE TABLE meetings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organizer_id INT NOT NULL,
  starts_at_utc TIMESTAMP NOT NULL,
  duration_minutes INT NOT NULL
) ENGINE=InnoDB;

All comparisons, sorting, and conflict detection happened purely in UTC:

SELECT * FROM meetings
WHERE starts_at_utc BETWEEN '2026-08-01 00:00:00' AND '2026-08-02 00:00:00'
ORDER BY starts_at_utc;

Display conversion happened only at the API/frontend layer using each user’s stored IANA time zone preference (e.g., Europe/Berlin), which is more maintainable than trying to juggle time zone state inside SQL session variables across a connection pool.

Performance Considerations

• CONVERT_TZ() with named zones requires the time zone tables to be loaded; if they’re missing, it silently returns NULL — I always test this after a fresh server setup.
• Filtering on a CONVERT_TZ()-wrapped column in a WHERE clause prevents index usage, since the function must be evaluated per-row. I filter on the raw UTC column whenever possible and only convert in the SELECT list.

-- Good: filters on indexed UTC column directly
SELECT * FROM meetings WHERE starts_at_utc >= '2026-08-01 00:00:00';

-- Avoid: prevents index usage
SELECT * FROM meetings WHERE CONVERT_TZ(starts_at_utc, 'UTC', 'America/New_York') >= '2026-08-01 00:00:00';

Security Considerations

Time zone strings from user input (e.g., a user_timezone field) should be validated against the known list in mysql.time_zone_name before being used in dynamic queries, since unchecked strings could otherwise be a vector for unexpected query behavior if concatenated directly into SQL rather than passed as bound parameters.

Common Mistakes I See with Time Zones

Some patterns that come up again and again in code reviews:

1. Mixing DATETIME and TIMESTAMP in the same table for related data. I’ve seen a table where created_at was TIMESTAMP (auto-converted) and scheduled_for was DATETIME (literal), and nobody on the team realized the two columns behaved completely differently until a report came out wrong during a DST transition.
2. Relying on the server’s SYSTEM time zone. If time_zone is left as SYSTEM, behavior silently depends on the OS configuration of whatever machine happens to be running MySQL — which can differ between a developer’s laptop, staging, and production.
3. Storing local time without also storing which zone it’s local to. A DATETIME value like 2026-08-01 14:00:00 is meaningless on its own unless you also know whose “14:00” that is — I always pair local-time columns with an explicit zone identifier column.
4. Assuming fixed UTC offsets are equivalent to named time zones. +05:00 never changes, but Asia/Karachi and similar named zones can have historical or future rule changes tied to legislation — using offsets loses that nuance entirely for date arithmetic across DST boundaries in regions that do observe DST.
5. Not reloading time zone tables after an OS update. DST rules occasionally change (a country adjusts its policy), and if the OS tzdata package updates but nobody re-runs mysql_tzinfo_to_sql, MySQL keeps using stale rules indefinitely.

Troubleshooting Common Issues

Interview Questions on MySQL Time Zones

1. What’s the key behavioral difference between DATETIME and TIMESTAMP regarding time zones? TIMESTAMP is stored internally as UTC and converted to/from the session’s time zone; DATETIME stores the literal value with no conversion at all.
2. Why is storing everything in UTC generally recommended? It removes ambiguity, avoids daylight saving time edge cases, and makes stored timestamps directly comparable regardless of where or when they were inserted.
3. How do you convert a UTC value to a specific local time zone in a query? CONVERT_TZ(utc_column, 'UTC', 'Target/Zone').
4. What must be done before named time zones like America/Chicago can be used? The time zone tables must be populated using mysql_tzinfo_to_sql.
5. Why can filtering with CONVERT_TZ() in a WHERE clause hurt performance? It’s a per-row function evaluation that prevents the optimizer from using an index on the underlying column.

Frequently Asked Questions

Q: Should I ever use DATETIME instead of TIMESTAMP? A: Yes — for values that represent a fixed wall-clock moment regardless of time zone, like a birthday or a scheduled “local business hours” value, DATETIME is often more appropriate since you don’t want it silently reinterpreted.

Q: Does TIMESTAMP‘s year-2038 limit matter? A: For most applications, not yet, but for long-lived data (e.g., mortgage schedules decades out), I use DATETIME to avoid the range limitation entirely.

Q: Can I store a UTC offset alongside a DATETIME instead of using TIMESTAMP? A: Yes, and I’ve done this — a DATETIME column paired with a separate VARCHAR time zone or offset column gives full control without relying on MySQL’s automatic conversion behavior.

Q: How does replication handle time zones? A: TIMESTAMP values replicate as UTC internally, so replication is consistent regardless of each server’s local session time zone setting — another reason I favor UTC-based storage.

Summary and Key Takeaways

Time zone handling is one of those areas where a small amount of upfront discipline saves enormous debugging pain later. My consistent approach: store timestamps in UTC, keep the server/session time zone fixed at UTC, and convert only at the display layer using CONVERT_TZ() or application code with a proper IANA time zone identifier.

Key takeaways:

• Understand the fundamental difference between TIMESTAMP (converted) and DATETIME (literal, no conversion).
• Load and periodically refresh the named time zone tables with mysql_tzinfo_to_sql.
• Store UTC, convert for display — never store local time as your source of truth.
• Avoid filtering on CONVERT_TZ()-wrapped columns to preserve index usage.
• Validate any user-supplied time zone strings against MySQL’s known zone list.

References

• MySQL Reference Manual: MySQL Server Time Zone Support
• MySQL Reference Manual: Date and Time Types
• MySQL Reference Manual: Date and Time Functions (CONVERT_TZ)