Changing time zones

Morthane · #1 (**permalink**) 01-17-06, 18:13

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

Morthane · #2 (**permalink**) 01-19-06, 17:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech