Posted inMySQL

MySQL Timezone Handling: Don’t Let Your App Get Lost Across Time Zones

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Timezone Offsets – An Old Problem That Never Gets Old

Timezone offsets are a leading cause of “all-nighter” support sessions. A common scenario: local code runs smoothly, but after deploying to AWS (which usually defaults to UTC), report data is suddenly off by 7-8 hours. A customer orders at 10 AM, but the system records it as 3 AM.

Incorrect initial design is a “death sentence” for databases when scaling up. Migrating billions of records later will take weeks instead of hours. I’ve seen a team stay up for 48 hours straight just to run a script converting 50 million rows of data. All because they stored local time instead of standardizing to UTC.

Quick Start: Check and Configure in a Heartbeat

Don’t guess. Check which timezone your database is currently running by using the following command:

-- View Global, Session time zones and current time
SELECT @@global.time_zone, @@session.time_zone, NOW();

If you see SYSTEM, MySQL is using the operating system’s time. This is the default setting but is extremely risky when moving servers between regions (e.g., from Singapore to the US).

To force the current session to UTC, use:

SET time_zone = '+00:00';
SELECT NOW(); -- Time will immediately match international time

Want a permanent configuration? Add this line to your my.cnf (Linux) or my.ini (Windows) file under the [mysqld] tag:

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

TIMESTAMP or DATETIME? A Choice That Decides Your Fate

Pros often have heated debates about these two data types. Each comes with its own “price”:

1. TIMESTAMP Type (4 Bytes)

  • Mechanism: MySQL automatically converts from the current timezone to UTC when saving and converts back when retrieving.
  • Limit: It will “die” on January 19, 2038 (the Y2K38 problem).
  • Plus: Automatically rotates based on the connecting client’s timezone.

2. DATETIME Type (5-8 Bytes)

  • Mechanism: Stores the original value (What you see is what you get). No transformation, no conversion.
  • Limit: Can store values up to the year 9999.
  • Minus: If the server region changes and you don’t handle the logic at the App layer, the data context will be incorrect.

Real-world advice: For modern systems, use DATETIME combined with forcing the Server to run in UTC. This ensures data transparency, makes debugging easier, and avoids the 2038 limit.

Advanced: Using Named Time Zones Like a Pro

Instead of memorizing a dry +07:00 offset, you can use 'Asia/Ho_Chi_Minh'. However, MySQL doesn’t include these name tables by default. On Linux, you need to load the data from the OS into the DB using this command:

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

Once loaded, setting the timezone becomes extremely intuitive. The biggest advantage is that MySQL will automatically handle Daylight Saving Time (DST) for European and American markets without you needing to change any code.

The “3-Layer” Strategy for Global Applications

To ensure your system never has the wrong time, apply this standard formula:

  1. Database Layer: Always hard-fix time_zone = '+00:00'. Use only DATETIME for storage.
  2. Application Layer: The Backend always parses every input to UTC before inserting. For example, Node.js uses moment.utc() or dayjs.utc().
  3. Client Layer: The Frontend receives a UTC string from the API and uses the browser to display it according to the user’s local time.

Warning: Don’t Let Timezones Slow Down Your Queries

The most common mistake is using conversion functions directly in the WHERE clause. With a table of 10 million records, the query below will cause a Full Table Scan, making server CPU usage spike to 100%:

-- DISASTER: Index is disabled
SELECT * FROM orders 
WHERE CONVERT_TZ(created_at, '+00:00', '+07:00') > '2023-10-01 00:00:00';

Solution: Calculate the comparison value on the App side first, then pass it into the query. Keep the created_at column in its “original” state so MySQL can utilize the Index.

-- STANDARD: Query speed in milliseconds
SET @search_time = '2023-09-30 17:00:00'; 
SELECT * FROM orders WHERE created_at > @search_time;

Key Takeaways

  • Say no to SYSTEM: Explicitly specify the timezone in the config file to avoid OS dependency.
  • UTC is the way: Store everything in UTC. Only convert to local time at the display layer (Frontend).
  • Docker Note: MySQL containers usually run in UTC, but a dev machine might be GMT+7. Use the environment variable TZ=Asia/Ho_Chi_Minh when running containers to stay synchronized.
  • Check Your Drivers: Some libraries like mysql2 (Node.js) or Eloquent (Laravel) may automatically convert times. Read the documentation carefully to disable this feature if you want manual control.

Handling time zones isn’t technically difficult, but it requires discipline from the entire team. Hopefully, these insights help you avoid costly mistakes due to time errors.

Share:
Tags: