COLINUXで利用していた、MYSQLのTimeZoneがEDTになっていてずれて
いたので、正しい時間が表示されていなかったので以下のように調整してみました。

※基本的には、SYSTEM時間を利用する設定になっているのは、TIME_ZONEの設定は
 変更しなくても問題ありません。今回の検証では、OSの時間がずれているのでもともと
 MYSQLの時間がずれていたのでOS時間はそのままで、MYSQLの時間を正しい時間に
 してみました。 外資系の会社で働いていてOSの時間は変えられないけれどもMYSQLの
 じかんは日本にあわせたい場合などに利用できるかも……


mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2009-07-12 00:10:53 |
+---------------------+
1 row in set (0.00 sec)

mysql>


[root@colinux bin]# pwd
/usr/local/mysql5130/bin
[root@colinux bin]# ls -l mysql_tzinfo_to_sql
-rwxrwxr-x 1 root mysql 2005715 2008-11-15 17:53 mysql_tzinfo_to_sql
[root@colinux bin]# /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
[root@colinux bin]#

timezone_insert


mysql> use STUDY
Database changed
mysql> set @@time_zone='JPT';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'JPT'
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (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> show variables like '%time_zone';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | EDT |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.01 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 @@time_zone='Japan';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%time_zone';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | EDT |
| time_zone | Japan |
+------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | Japan |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> update TIMESTAMP_FUNC set data ='After time_zone_change' where created ='2009-07-11 10:22:23'
-> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------------+
| 2009-07-11 23:12:10 | 2009-07-11 23:13:51 | This is timestmp update test |
| 2009-07-11 23:22:21 | 2009-07-11 23:22:21 | This is timestamp test2 |
| 2009-07-11 23:22:23 | 2009-07-11 23:23:04 | This is timestmp update test |
+---------------------+---------------------+------------------------------+
3 rows in set (0.01 sec)

mysql>

timezone_insert2

————————————————————————————-
TIME_ZONE変更前
————————————————————————————-


mysql> show variables like '%time_zone';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | EDT |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (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> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------------+
| 2009-07-11 10:12:10 | 2009-07-11 10:13:51 | This is timestmp update test |
| 2009-07-11 10:22:21 | 2009-07-11 10:22:21 | This is timestamp test2 |
| 2009-07-11 10:22:23 | 2009-07-11 10:23:04 | This is timestmp update test |
+---------------------+---------------------+------------------------------+
3 rows in set (0.00 sec)

————————————————————————————————————-
TIME_ZONE変更後 (データは特に変更していない。自動的に表示がTime_Zoneに変更される。
————————————————————————————————————-


mysql> show variables like '%time_zone';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | EDT |
| time_zone | Japan |
+------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | Japan |
+--------------------+---------------------+
1 row in set (0.00 sec)

time_stamp_current

mysql> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------------+
| 2009-07-11 23:12:10 | 2009-07-11 23:13:51 | This is timestmp update test |
| 2009-07-11 23:22:21 | 2009-07-11 23:22:21 | This is timestamp test2 |
| 2009-07-11 23:22:23 | 2009-07-11 23:23:04 | This is timestmp update test |
+---------------------+---------------------+------------------------------+
3 rows in set (0.01 sec)

time_stamp_3

恒久的に反映させるには、OPTIONファイル(my.cnf)にて設定を入れる必要があります。
サーバのタイム ゾーンの初期グローバル値は、コマンドラインで –default-time-zone=timezone
オプションで起動するときに明示的に指定する。または、オプション ファイルに次のラインを使用する。

default-time-zone=’timezone’

参考URL

4.10.8. MySQL サーバのタイム ゾーン サポート


第4章 データベース管理

============================================
良いタイミングなので、Colinuxの時間を合わせておきました。。。。
============================================
[root@colinux bin]# date
2009年 7月 11日 土曜日 11:14:08 EDT

[root@colinux bin]# ls -l /etc/localtime
-rw-r–r– 1 root root 3519 2008-11-10 14:35 /etc/localtime

[root@colinux bin]# cp /usr/share/zoneinfo/Japan /etc/localtime

[root@colinux bin]# date -s “2009/07/12 00:28”
2009年 7月 12日 日曜日 00:28:00 JST
[root@colinux bin]# date
2009年 7月 12日 日曜日 00:29:13 JST
[root@colinux bin]#


現在のTIMEZONEの調査と設定変更について


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 session time_zone ='+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | +09:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql>

timezone1

TIME ZONEによってテーブルの中のデータ表示も自動的に変わります。


mysql> desc TINT;
+-------+------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+-------------------+-------+
| ID | tinyint(2) | YES | | NULL | |
| TimeS | timestamp | YES | | CURRENT_TIMESTAMP | |
+-------+------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into TINT(ID) values(101);
Query OK, 1 row affected (0.00 sec)

mysql> select * from TINT;
+------+---------------------+
| ID | TimeS |
+------+---------------------+
| 1 | 2009-07-07 01:45:00 |
| 2 | 2009-07-07 01:45:03 |
| 100 | 2009-07-07 01:45:11 |
| 127 | 2009-07-07 01:45:19 |
| 127 | 2009-07-07 01:45:22 |
| 100 | 2009-07-07 02:40:38 |
| 101 | 2009-07-07 02:41:15 |
+------+---------------------+
7 rows in set (0.00 sec)

mysql> set session time_zone ='+01:00';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from TINT;
+------+---------------------+
| ID | TimeS |
+------+---------------------+
| 1 | 2009-07-06 17:45:00 |
| 2 | 2009-07-06 17:45:03 |
| 100 | 2009-07-06 17:45:11 |
| 127 | 2009-07-06 17:45:19 |
| 127 | 2009-07-06 17:45:22 |
| 100 | 2009-07-06 18:40:38 |
| 101 | 2009-07-06 18:41:15 |
+------+---------------------+
7 rows in set (0.00 sec)

mysql> set session time_zone ='+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from TINT;
+------+---------------------+
| ID | TimeS |
+------+---------------------+
| 1 | 2009-07-07 01:45:00 |
| 2 | 2009-07-07 01:45:03 |
| 100 | 2009-07-07 01:45:11 |
| 127 | 2009-07-07 01:45:19 |
| 127 | 2009-07-07 01:45:22 |
| 100 | 2009-07-07 02:40:38 |
| 101 | 2009-07-07 02:41:15 |
+------+---------------------+
7 rows in set (0.01 sec)

mysql>

timezone2

——————————————

mysql> CREATE TABLE ts_tz_test (ts TIMESTAMP);
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT NOW(); INSERT INTO ts_tz_test VALUES (NOW());
+---------------------+
| NOW() |
+---------------------+
| 2009-07-07 10:54:35 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> SELECT ts FROM ts_tz_test;
+---------------------+
| ts |
+---------------------+
| 2009-07-07 10:54:35 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = '+09:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ts FROM ts_tz_test;
+---------------------+
| ts |
+---------------------+
| 2009-07-07 23:54:35 |
+---------------------+
1 row in set (0.01 sec)

mysql>

local sessionともう一つ立ち上げたオリジナルセッションの値比較。
When changing the session time zone, the server converts the display
(but not the value stored in the ts column) accordingly; this would not
have happened if you had changed the global time zone.

timezone3