How to Set Timezone in MySQL

MySQL time-zone settings affect time-zone-sensitive behavior, including NOW() and conversion of TIMESTAMP values; they do not mean every date and time type, such as DATETIME, is converted by the session time zone. To choose the right setting, first inspect the global and current-session values. Then decide whether the change should survive a server restart, apply to new connections, or affect only your current connection. Named zones such as Europe/Budapest can use daylight-saving rules when MySQL has the required time-zone data; a numeric offset is fixed and does not carry those rules.

Check the Current Time Zone in MySQL

NOW() returns the current date and time in the current session’s time zone. Its value alone does not identify the configured time zone. Check the global and session settings as well:

SELECT NOW(), @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

@@GLOBAL.time_zone is the server-wide default used to initialize a connection’s session setting. @@SESSION.time_zone is the value used by the current connection and can differ from the global value. When either setting is SYSTEM, MySQL uses the operating system’s time zone for that scope. system_time_zone reports the system time-zone name detected by the server; it does not show a named zone selected through a MySQL setting.

Use these values together before changing anything. If the session setting differs from the global setting, changing the server default will not change the time zone already assigned to that existing connection.

Set the Default Time Zone in MySQL Configuration

To make a default persist across server restarts, set default-time-zone in the MySQL server configuration. Find the configuration file used by your installation, then add the setting under its [mysqld] section. For example, default-time-zone = "+00:00" selects a fixed UTC offset. Configuration file locations and service names vary by operating system and installation; use the path and restart procedure for your environment.

If the file has no [mysqld] section, add the section before the setting. Restart the MySQL service for the configuration change to take effect. A restart does not make a regional time-zone choice more durable: use a named zone if you need its changing daylight-saving rules and the server has the corresponding time-zone data.

Change the Global Time Zone at Runtime

A runtime global change updates the server’s global time_zone value without editing the configuration file. For example, SET GLOBAL time_zone = '+00:00'; selects a fixed UTC offset. A named-zone value can be set with SET GLOBAL time_zone = 'Europe/Budapest'; when that zone is available in MySQL’s time-zone tables.

This setting affects the default assigned to new connections. It does not change the session setting of connections that are already open, and it is not a persistent configuration change: a server restart returns to the configured default. The operator must have suitable access to change the global variable; the required access can depend on the MySQL version and setup.

A numeric offset is useful when a fixed offset is intended. Do not use a regional offset as a permanent substitute for a named zone: the offset for a region can change during the year, while a fixed value such as -04:00 has no daylight-saving rules.

Set a Time Zone for the Current Session

Set time_zone for the current connection when only that connection needs a different interpretation of times. For example, SET time_zone = '+00:00'; changes the session to a fixed UTC offset. To use a region’s rules instead, set a supported named value such as SET time_zone = 'Europe/Budapest';.

@@SESSION.time_zone then reflects the value for this connection; @@GLOBAL.time_zone remains unchanged. The change lasts for the connection, not until a server restart. A new connection receives a session value initialized from the current global setting, unless that connection changes its own session value.

Troubleshoot Named Time Zones and Daylight Saving Time

If MySQL reports Unknown or incorrect time zone after you specify a name such as Europe/Budapest, the server may not have usable time-zone table data, or its data may be out of date. Numeric offsets do not require a named-zone lookup, but they also cannot represent daylight-saving transitions. Check the value you supplied and whether the server’s time-zone tables have been populated and maintained for your environment.

See MySQL’s documentation on Populating the Time Zone Tables for the supported procedure. After the data is available, retry the named-zone setting and verify both @@SESSION.time_zone and @@GLOBAL.time_zone at the scope you intended. Named zones apply daylight-saving rules only according to the time-zone data available to the server; this article cannot determine whether a particular installation’s data is current.