Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    20

    Unanswered: Changing time zones

    I'm using MySQL 5.0 and I want to set my database's time zone to something other than SYSTEM_TIME.

    1. GMT or UTC? What is the current standard?
    2. I have tried
    Code:
    set time_zone = 'UTC';
    but MySQL does not recognize the time zone
    3. I want this as a session-independent setting (ie, I want my DB to stay in UTC or whatever for every time I log in).
    4. Can I set this for the current database and not effect the default/global setting? I think that's what I intended in the above code by omitting the 'global' keyword...

    EDIT: I doubt this matters, but this is on a Windows box
    Last edited by Morthane; 01-17-06 at 19:35.

  2. #2
    Join Date
    Jan 2006
    Posts
    20
    Found the answer. Since Windows computers don't have zoneinfo files, MySQL can't use information like 'UTC' or 'PST'. A fix can be found here to give you timezone tables.

    Alternately, you can use absolute values. Below is the code for viewing and setting the time zone. Omit the GLOBAL tag if you would rather set the session time.

    Code:
    mysql> SET GLOBAL time_zone = SYSTEM;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | SYSTEM             | SYSTEM              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> SET GLOBAL time_zone = '+00:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | +00:00             | SYSTEM              |
    +--------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql>
    I have also learned that it is a poor choice to trust the MySQL timezone handling if possible - use PHP, C/C++/C#, or whatever else you have to perform the calculations before/after storage - discussion here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •