TIMESTAMP列はDEFAULTでは、NOT NULLに設定されます。
あらかじめNULL値が入る事を想定している場合は明示的にNULLを
指定してテーブルを作成する。

以下テーブル作成後のALTER TABLEにて属性変更している。


mysql> CREATE TABLE timestamp_null (
-> data_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE timestamp_null
-> MODIFY data_time TIMESTAMP NULL
-> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>


mysql> INSERT INTO timestamp_null (data_time, i) VALUES (NULL, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+-----------+------+
| data_time | i |
+-----------+------+
| NULL | 10 |
+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_null (data_time, i) VALUES (now(), 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+---------------------+------+
| data_time | i |
+---------------------+------+
| NULL | 10 |
| 2009-07-15 04:31:42 | 10 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql>

null_timestamp

TIMESTAMP関連の検証


mysql> CREATE TABLE timestamp_chk (data_time TIMESTAMP NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> desc timestamp_chk;
+-----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| data_time | timestamp | YES | | NULL | |
+-----------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO timestamp_chk VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+-----------+
| data_time |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES ('文字列');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 | ← 文字列は対象外なので"0" Valueがinsertされる。
+---------------------+
2 rows in set (0.00 sec)

mysql>

timestamp_check

日付列に12 Digit と14 DigitのNumberをInsertした時の違い


mysql> INSERT INTO timestamp_chk VALUES (200202082139);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES (20020208213900);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
+---------------------+
4 rows in set (0.00 sec)

timestamp_digit

無効な日付をINSERTした場合

    ※2月31日は存在しない日付

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-31 23:59:59');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

    ※60秒が指定されている。

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-28 23:59:60');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql>

timestamp_digit_2


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]#


テーブルに対してINSERT処理をすると同時にINSERTされた時間が
データが作成された時間列(created )とデータが更新された時間列(updated)
に同時に格納されるには以下のようにTIMESTAMP列に対して
”DEFAULT 0”と” on update CURRENT_TIMESTAMP”をDEFAULT値として
設定しておく。


CREATE DATABASE `STUDY` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE TABLE TIMESTAMP_FUNC (
created TIMESTAMP DEFAULT 0,
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
data CHAR(30)
);

mysql> CREATE TABLE TIMESTAMP_FUNC (
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc TIMESTAMP_FUNC;
+---------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+-----------------------------+
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| updated | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| data | char(30) | YES | | NULL | |
+---------+-----------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> insert into TIMESTAMP_FUNC(created,updated,data) values(NULL,NULL,'This is timestamp test');
Query OK, 1 row affected (0.00 sec)

mysql> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------+
| 2009-07-11 10:12:10 | 2009-07-11 10:12:10 | This is timestamp test |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

mysql> update TIMESTAMP_FUNC set data ='This is timestmp update test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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 |
+---------------------+---------------------+------------------------------+
1 row in set (0.00 sec)

mysql>

以下のようにデータが更新された列のupdate列はデータ更新日になっている。

確認①
time_stamp
確認②
time_stamp_2


ON UPDATE CURRENT_TIMESTAMP列を作成すると
データを更新した日付が自動的に変更される。


mysql> CREATE TABLE `BBS2` (
-> `comment` varchar(100) NULL DEFAULT NULL,
-> `comment_time` timestamp NULL DEFAULT 0,
-> `comment_update_time` timestamp ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc BBS2;
+---------------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------------------+-----------------------------+
| comment | varchar(100) | YES | | NULL | |
| comment_time | timestamp | YES | | 0000-00-00 00:00:00 | |
| comment_update_time | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+---------------------+--------------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)

current_timestamp2

mysql> insert into BBS2(comment) values('Hello World!');
Query OK, 1 row affected (0.00 sec)

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into BBS2(comment) values('Hello World!');
Query OK, 1 row affected (0.00 sec)

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into BBS2(comment) values('Hello World');
Query OK, 1 row affected (0.00 sec)

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> update BBS2 set comment = 'Hello World!' where comment ='Hello World';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 2009-07-09 12:39:05 | <----更新されている。 +--------------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql>

current_timestamp


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


TIMESTAMP型

TIMESTAMP カラムは DATETIME カラムと同じフォーマットで表示されます。
言い換えると、表示幅は19文字に決められていて、フォーマットは YYYY-MM-DD HH:MM:SS
となります。


mysql> CREATE TABLE timestamp01 (ts1 TIMESTAMP, i INT);
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE timestamp02 (
-> ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc timestamp01;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> desc timestamp02;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| ts1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>

timestamp


mysql> INSERT INTO timestamp01 SET ts1=NULL, i=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp01 SET ts1='', i=10;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO timestamp01(i) values(10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp01(ts1,i) values(NULL,10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp01;
+---------------------+------+
| ts1 | i |
+---------------------+------+
| 2009-07-05 09:23:51 | 10 |
| 0000-00-00 00:00:00 | 10 | ←

    MYSQLの設定にてエラーになる事もあります。

| 2009-07-05 09:24:22 | 10 |
| 2009-07-05 09:24:29 | 10 |
+---------------------+------+
4 rows in set (0.00 sec)

mysql>

timestamp1


mysql> INSERT INTO timestamp02 SET ts1=NULL, i=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp02 SET ts1='', i=10;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'ts1' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp02(i) values(10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp02(ts1,i) values(NULL,10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp02;
+---------------------+------+
| ts1 | i |
+---------------------+------+
| 2009-07-05 09:29:10 | 10 |
| 0000-00-00 00:00:00 | 10 |←

    MYSQLの設定にてエラーになる事もあります。

| 2009-07-05 09:29:39 | 10 |
| 2009-07-05 09:29:48 | 10 |
+---------------------+------+
4 rows in set (0.00 sec)

mysql>

timestamp2

MySQLサーバは MAXDB SQLモードが有効な時も実行する事ができます。
このモードが有効な状態でサーバが実行された時、TIMESTAMP は DATETIME
と同一です。これは、もしテーブルが作成された時にこのモードが有効だと、
TIMESTAMP カラムは DATETIME カラムとして作成される、という意味になります。
その結果、そのようなカラムは DATETIME 表示フォーマットを利用し、同じ範囲の値を持ち、
自動初期化機能や、現在の日付と時刻に自動的にアップデートする機能はないという事になります。

MAXDB モードを有効にするには、起動の際に、–sql-mode=MAXDB サーバオプションを
利用するか、ランタイム時にグローバル sql_mode 変数を設定して、サーバSQLのモードを
MAXDB に設定してください。

mysql> SET GLOBAL sql_mode=MAXDB;

クライアントは接続の為に、次のようにサーバを MAXDB モードで起動させる事ができます。

mysql> SET SESSION sql_mode=MAXDB;

次に紹介されている情報は、MAXDB モードが有効な状態で作成されなかった
テーブルだけの TIMESTAMP カラムに適合するという事を覚えておいて下さい。
なぜならば、そのようなカラムは DATETIME カラムとして作成されるからです。

MySQLは、日付か月のカラムにゼロを含むタイムスタンプ値や、有効でない
日付値は許容しません。このルールの唯一の例外は、’0000-00-00 00:00:00′ の特別値です。

————————————————-
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

timestamp3

参考ページ
10.3.1.1. TIMESTAMP MySQL 4.1での性質