How To Change TimeZone of MySQL

The MySQL server maintains several time zone settings

  • The system time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable. The value does not change thereafter.

    You can change timezone of MySQL Server at startup with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld. The permissible values for --timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable.

  • The server's current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

    The initial global server time zone value can be specified explicitly at startup with the --default-time-zone=timezone option on the command line, or you can use the following line in an option file (my.cnf):

    default-time-zone='timezone'
    #default_time_zone='+07:00'
    

    If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

    mysql> SET GLOBAL time_zone = timezone;
    #mysql> SET GLOBAL time_zone = '+8:00';
    #mysql> SET GLOBAL time_zone = 'Europe/Helsinki';
    #mysql> SET @@global.time_zone = '+00:00';
    
  • Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

    mysql> SET time_zone = timezone;
    #mysql> SET time_zone = 'Europe/Helsinki';
    #mysql> SET time_zone = '+00:00';
    #mysql> SET @@session.time_zone = '+00:00';
    

The current values of the global and client-specific time zones can be retrieved like this:

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

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections