現在の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

Comments are closed.

Post Navigation