Change MySQL Time Zone: 3 Ways to Set It
To change the MySQL time zone, you can set the global or session time zone with the SET time_zone command, or modify the server’s default time zone in the MySQL configuration file. The exact method depends on whether you want a permanent server-wide change, a change for a single connection, or a change for a specific query.
Understanding MySQL Time Zones
MySQL stores time zone information in two forms: the system time zone (used by the server for internal operations) and the session time zone (used for displaying and interpreting TIMESTAMP values). By default, MySQL uses the server’s operating system time zone, which is often UTC on cloud-hosted databases. The session time zone can be changed at runtime without restarting the server, making it a flexible tool for applications that serve users across multiple regions.
It’s important to distinguish between TIMESTAMP and DATETIME columns. TIMESTAMP values are stored in UTC and converted to the session time zone for display. DATETIME values are stored as-is, with no time zone conversion. This distinction is the root of most time zone confusion in MySQL.
Checking the Current Time Zone
Before changing anything, you need to know what time zone MySQL is currently using. The following queries return the global and session time zone values:
SELECT @@global.time_zone;
SELECT @@session.time_zone;
If the result is SYSTEM, MySQL is using the operating system’s time zone. You can also see the current UTC timestamp and the local timestamp to compare:
SELECT NOW(), UTC_TIMESTAMP();
Setting the Time Zone for a Session
To change the time zone for just your current connection (session), use:
SET time_zone = '+05:30'; -- for India Standard Time
SET time_zone = 'America/New_York'; -- requires time zone tables
The session setting applies to all TIMESTAMP values you retrieve and insert. It does not affect other connections. This is useful when your application needs to display times in a user’s local time zone.
You can use an offset like '+05:30' or a named time zone like 'America/New_York'. Named time zones require the MySQL time zone tables to be loaded (see below).
Setting the Global Time Zone
To change the time zone for all new connections (but not the current session), use:
SET GLOBAL time_zone = '+00:00'; -- UTC
This requires the SYSTEM_VARIABLES_ADMIN privilege (or the older SUPER privilege). The change takes effect immediately for new sessions, but existing sessions keep their old time zone until they reconnect.
If you want the global time zone to persist across server restarts, you must also set it in the configuration file.
Setting the Default Time Zone in the Configuration File
To make a permanent change, edit the MySQL configuration file (usually my.cnf or my.ini) and add a line under the [mysqld] section:
[mysqld]
default-time-zone = '+00:00'
Then restart the MySQL service. This sets the server’s default time zone, which is used when a session does not specify its own time zone. The value can be an offset or a named time zone (if the time zone tables are loaded).
Loading Named Time Zone Tables
To use named time zones like 'America/New_York', you need to populate the MySQL time zone tables. On most systems, you can do this with the mysql_tzinfo_to_sql utility:
- Find your system’s time zone database (usually in
/usr/share/zoneinfoon Linux). - Run:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql - This imports the time zone data into the
mysqldatabase.
After loading, you can use named time zones in SET statements. If you don’t load these tables, you can still use offsets like '+05:30'.
Common Mistakes and Pitfalls
- Confusing TIMESTAMP and DATETIME: Changing the time zone only affects
TIMESTAMPcolumns.DATETIMEcolumns are not converted. - Forgetting to reload time zone tables: If you use named time zones without loading the tables, MySQL returns an error.
- Using SYSTEM when the OS time zone is wrong: If the server’s OS time zone is not UTC,
SYSTEMcan cause inconsistencies. - Not persisting the change: A
SET GLOBALdoes not survive a restart unless also set in the config file.
Frequently Asked Questions
Does changing the time zone affect existing data?
No. TIMESTAMP values are stored in UTC internally, so changing the session time zone only changes how they are displayed. DATETIME values are stored as-is, so they remain unchanged.
What is the difference between SYSTEM and UTC?
SYSTEM means MySQL uses the operating system’s time zone. UTC is the coordinated universal time zone. Using UTC is recommended for consistency, especially in distributed systems.
Can I set a time zone for a single query?
Yes, you can use the CONVERT_TZ() function to convert a value to a different time zone in a query, without changing the session time zone. For example: SELECT CONVERT_TZ('2025-01-01 12:00:00', '+00:00', '+05:30');
Why do I get an error when using a named time zone?
You likely haven’t loaded the time zone tables. Run the mysql_tzinfo_to_sql command as described above, or use an offset instead.
Does changing the time zone affect NOW() and CURDATE()?
Yes. NOW() and CURDATE() return the current time in the session time zone. UTC_TIMESTAMP() always returns UTC.