Setting the timezone in different format in mysql

Steps for changing the timezone:

First we need to run the mysql_tzinfo_to_sql program, provided with the MySQL .
mysql_tzinfo_to_sql reads the operating system time zone files and generates SQL statements from them.
The SQL statements are then processed by mysql and loads the data int the time zone tables.

To run mysql_tzinfo_to_sql successfully,we should know the server machine's operating system
time zone files are stored; check for a directory with a name similar to /usr/share/zoneinfo. 

1. First try to find the mysql_tzinfo_to_sql utility

 locate mysql_tzinfo_to_sql
 shell>  /usr/bin/mysql_tzinfo_to_sql

2. Initially we need to insert the records in mysql tables "time_zone" and "time_zone_name".
The output is as follows:

mysql> select * from time_zone;
Empty set (0.00 sec)

mysql> select * from time_zone_name;
Empty set (0.00 sec)

3. For inserting the records in time_zone the command is as follows:

shell >  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u kiran -pkiran mysql

4.just make sure the records are inserted on the two tables.  

mysql> select count(*)  from mysql.time_zone;
+----------+
| count(*) |
+----------+
|     1690 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*)  from mysql.time_zone_name;
+----------+
| count(*) |
+----------+
|     1690 |
+----------+

4.a) run the following command to know more about the data

select * from time_zone_transition_type;
select * from time_zone_transition;
select * from time_zone_name;
select * from time_zone;


5. Now say for example we want to change the mysql timezone to UTC.
for example the system timezone is in IST , but we want the mysqltime to be in UTC format.

we need to modify in my.cnf file as follows:

[mysqld]
default_time_zone=UTC

[mysqld_safe]
timezone=UTC

6. we need to restart the mysql.

6. connect to mysql and check the date.
we can see different time in mysql and systemtime

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2011-06-08 06:22:13 |
+---------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
[root@hostname kiran.chinta]# date
Wed Jun  8 11:52:18 IST 2011


7. checking for mysql timezone ;

mysql> show variables like 'time%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| time_format   | %H:%i:%s   |
| time_zone     | UTC        |
| timed_mutexes | OFF        |
| timestamp     | 1307514250 |
+---------------+------------


mysql> SELECT CONVERT_TZ('2007-10-28 23:00:00', '-5:00', '+0:00');
+-----------------------------------------------------+
| CONVERT_TZ('2007-10-28 23:00:00', '-5:00', '+0:00') |
+-----------------------------------------------------+
| 2007-10-29 04:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)


mysql> select CONVERT_TZ(now(), '+0:00', 'SYSTEM');
+--------------------------------------+
| CONVERT_TZ(now(), '+0:00', 'SYSTEM') |
+--------------------------------------+
| 2011-06-08 06:37:08                  |
+--------------------------------------+
1 row in set (0.00 sec)

To know more about the timezone given below are the URL

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html